Removing embedded symbols

[doloresmeehan]

Hi,

I am trying to remove the ~ symbol that is embedded from a large text field. Is there a way to do that?

Thanks,
Dolores

 

[markros]

Well, you need to use REPLACE() function in T-SQL for that.

Can you tell a bit more of your problem?

update myTable set MyFieldWithTilda = replace(MyFieldWithTilda,'~','') where charindex('~',MyFieldWithTilda) > 0

 

[doloresmeehan]

Thanks for your help.

Here's a sample of the data in one record (snoteText) of the table sNote:

"SATIN PILLOW CASE SET;;FABRIC: 200T-210T 100%POLYESTER SATIN WEAVE, 75DX100D, SOLID COLOR;;~;~STYLE: 2"" HEM OPENING, DOUBLE STITCHING.;;LABEL: CARE LABEL;PACKING: VZB+INSERT; 6 PCS PER CARTON ;SIZE: 20X30"";COLOR: BLACK;HTS#6304.93.0000;"

Thanks again.

 

[doloresmeehan]

So, I ran this script:

update snote set sNoteText = replace(sNoteText,'~','') where charindex('~',sNoteText) > 0

And received this error:

Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

Please help - did I do it right?

Thanks

 

[markros]

Ok, try

select ID, SnoteText, replace(SnoteText,'~','') as Fixed from myTable where SnoteText like '%~%'

to get the records with the ~ inside the text field and see if your select will return correct results.

If satisfied, then you may run the update I posted in my first post.

 

[gmmastros]

The replace function does not work with the TEXT data type. What version of SQL Server are you using? If you're not sure, run this:

Select @@Version

If you are running SQL2005 (or greater), then I would encourage you to change the data type of the snoteText column to varchar(max). The replace function does with with varchar(max) data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Sorry, didn't see your previous post when I answered. George answer is correct then, you may try:

select ID, SnoteText, replace(cast(SnoteText as varchar(8000)),'~','') as Fixed from myTable where SnoteText like '%~%'

if you're using SQL Server 2000

 

[gmmastros]

Of course.... if you have more than 8000 characters in your text column, casting it to varchar(8000) will cause you to lose data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[doloresmeehan]

Awesome - it worked. Thanks Guys! I did change the field type and then the replace script worked.

Thanks again to you both.

 

[markros]

You're welcome.