How can remove some Spacel Characters

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

 From farmhouse to industrial chic, from the urban loft all the way out selection!

remove this characters: 

for sql server

Posted 23-Feb-15 22:26pm

krishna97

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

hypermellow · Answer 1 · 2015-02-23T22:34:00

Solution 1

Hi,

Have a look at MSSQL REPLACE function.

You can use it to remove your html character, like:

SQL

SELECT        
REPLACE(REPLACE('<p> From farmhouse to industrial chic, from the urban loft all the way out selection!</p>', '<p>', '') , '</p>', '')

To replace multiple characters, you need to nest the REPLACE function.

[edit]Updated to replace start and end tags[/edit]

... hope it helps.

Posted 23-Feb-15 22:34pm

hypermellow

Updated 23-Feb-15 22:53pm

v3

Comments

krishna97 24-Feb-15 4:47am

end tag can not replace

From farmhouse to industrial chic, from the urban loft all the way out selection!

hypermellow 24-Feb-15 4:54am

... that's because you never asked to remove the start and end tags.

I've updated the above solution to remove start and end tags.

krishna97 24-Feb-15 4:58am

ok thanks

krishna97 24-Feb-15 5:02am

error given
Data type used text

this error can given
===========================
SELECT
REPLACE(REPLACE(ProductInfo, '', '') , '', '') from tbl_ProductInfo

my matter :
==
 From farmhouse to industrial chic, from the urban loft all the way out to the lodge, we have all rustic looks covered in floor lamps styles. If your style leans towards the naturalist looks check out the iron metal twigs or curling wood vine floor lamps. However, if you're feeling a little bit country or maybe even French provincial, don't miss our shabby vintage chic and pickled wood choices in floor lamps, often with matching table lamps. And if you're into mountain cabin chic, our oversized real antler or bronze modern craftsman floor lamps are great choices. Or how about an industrial pipe or safari animal floor lamp? We also have adjustable reading floor lamps. We hope you enjoy browsing our selection!

hypermellow 24-Feb-15 5:07am

You'll need to CAST or CONVERT your column to VARCHAR/NVARCHAR to use the REPLACE function.

Something like this should work:
SELECT
REPLACE(REPLACE(CAST(ProductInfo AS VARCHAR(MAX)), '', '') , '', '') from tbl_ProductInfo

Be aware that the conversion to VARCHAR may truncate your data.

OriginalGriff · Answer 2 · 2015-02-23T22:36:00

Don't.
While it's not difficult to remove those specific characters, it's a poor idea - it's very easy to get it wrong with a format as vague as HTML.

Instead, use one of the many, many HTML parsers you can find via Google to parse the entire document and then extract just the info you want from that.

Avadhesh Kumar Maurya · Answer 3 · 2015-02-23T23:12:00

To remove special characters you can use the below line of code. This will replace special characters only.

SQL

DECLARE @RegularExp INT
Declare @Str varchar(500)
SET @Str='<p> From farmhouse to industrial chic, from the urban loft all the way out selection!</p>'
SET @RegularExp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
WHILE @RegularExp > 0
BEGIN
SET @Str = STUFF(@Str, @RegularExp, 1, ' ' )
SET @RegularExp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
END
SELECT @Str

If you want to replace html tags then you will have to use replace function of sql.

How can remove some Spacel Characters

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0