Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Removing embedded symbols

Status
Not open for further replies.

doloresmeehan

Technical User
Jan 15, 2005
7
US
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
 
Well, you need to use REPLACE() function in T-SQL for that.

Can you tell a bit more of your problem?
Code:
update myTable set MyFieldWithTilda = replace(MyFieldWithTilda,'~','') where charindex('~',MyFieldWithTilda) > 0
 
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.
 
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
 
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.

 
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
 
Sorry, didn't see your previous post when I answered. George answer is correct then, you may try:
Code:
select ID, SnoteText, replace(cast(SnoteText as varchar(8000)),'~','') as Fixed from myTable where SnoteText like '%~%'

if you're using SQL Server 2000
 
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
 
Awesome - it worked. Thanks Guys! I did change the field type and then the replace script worked.

Thanks again to you both.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top