Replace string

Question

Replace string

PSB

SSChampion

Points: 13195
More actions
April 19, 2017 at 9:55 am

#320557

Hi I need to replace these values as blank space from a string
Hello all! &
#160; How Are you ?:
AS
Hello all! How Are you ?
Thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What have you tried?

You could use PATINDEX to search for '&#%;' and do the replace from there. You may need a string splitter - I'm not sure.

John

Thom A SSC Guru Points: 99126 More actions · Answer 2

You should be able to do this one yourself, considering how long you've been here 🙂

https://www.google.co.uk/search?q=REPLACE+%28Transact-SQL%29&ie=utf-8&oe=utf-8&client=firefox-b&gfe_rd=cr&ei=kIn3WI6ZL6n38AfxloHoCg#safe=strict&q=REPLACE+T-SQL

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

PSB - Wednesday, April 19, 2017 9:55 AM
Hi I need to replace these values as blank space from a string
Hello all! &
#160; How Are you ?:
AS
Hello all! How Are you ?
Thanks,

This should be "Hello all! How Are you :" unless you provide further logic.
😎

PSB SSChampion Points: 13195 More actions · Answer 4

PSB

SSChampion

Points: 13195

April 19, 2017 at 10:25 am

#1938876

right

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Would this work for you?
SELECT *, CAST(CAST(String AS XML) AS VARCHAR(MAX)) FROM (VALUES('Hello all! How Are you ?:'))x(String)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

This might be a slower method, but it will do exactly what you asked in your first post.
SELECT *, ( SELECT CASE WHEN s.Item LIKE '#[0-9]%' THEN STUFF( s.Item, 1, CHARINDEX(';', s.Item), ' ') ELSE s.Item END FROM dbo.DelimitedSplit8K( x.String, '&') s FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)') FROM (VALUES('Hello all! How Are you ?:'))x(String)
The splitter function is explained in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99126 More actions · Answer 7

I might be being daft, but is this not as simple as:
REPLACE(REPLACE('Hello all! How Are you ?:', ' ',''),':','')

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

Thom A - Wednesday, April 19, 2017 12:32 PM
I might be being daft, but is this not as simple as:
REPLACE(REPLACE('Hello all! How Are you ?:', ' ',''),':','')

I'm sure those are not the only two codes that need to be replaced.
https://www.w3.org/TR/html4/charset.html#h-5.3

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sgmunson SSC Guru Points: 110639 More actions · Answer 9

Luis Cazares - Wednesday, April 19, 2017 1:05 PM
Thom A - Wednesday, April 19, 2017 12:32 PM
I might be being daft, but is this not as simple as:
REPLACE(REPLACE('Hello all! How Are you ?:', ' ',''),':','')
I'm sure those are not the only two codes that need to be replaced.
https://www.w3.org/TR/html4/charset.html#h-5.3

And, there''s the rather slow performance for the REPLACE function over large numbers of records to worry about as well.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 10

sgmunson - Wednesday, April 19, 2017 2:22 PM
Luis Cazares - Wednesday, April 19, 2017 1:05 PM
Thom A - Wednesday, April 19, 2017 12:32 PM
I might be being daft, but is this not as simple as:
REPLACE(REPLACE('Hello all! How Are you ?:', ' ',''),':','')
I'm sure those are not the only two codes that need to be replaced.
https://www.w3.org/TR/html4/charset.html#h-5.3
And, there''s the rather slow performance for the REPLACE function over large numbers of records to worry about as well.

That's pretty much a myth unless you've used a collation that's a bit heavy handed. Nested replaces actually rock for performance when done correctly.

Do you have a link for the slow performance claim? I'd like to have a look and see what they've done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)