How to use T-SQL Replace Command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to use T-SQL Replace Command

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-19 : 09:51:59

Ken Powers writes "I tried out the t-sql replace command you explained on one of your pages. Unfortunately, I am not getting good results when I run it.

Here is the column in question:

description
-------------------------
‚CHOCOLATE CHIP‚
‚COOKIES‚
‚CROISSANTS *PLAIN*‚
‚DONUTS‚
‚DONUTS *DOZEN*‚
‚MUFFINS‚
‚BAGELS‚
‚ROLLS‚
‚CUPCAKES‚
‚CRISPIES‚
‚DANISH/SWEET ROLLS‚
‚FUDGE BROWNIES‚
‚PUFF PASTRIES/ECCLES‚
‚STICKY BUNS‚
‚TURNOVERS‚
‚BLACK & WHITE COOKIES‚
‚LINZER TARTS‚
‚SCONES/BISCUITS‚
‚SCUFFINS‚
‚SINFULL BITS‚

I need to remove the commas from this column, so I wrote a replace statement:

UPDATE ItemInformation
SET Description = REPLACE(',', ',', '')

Unfortuately, I end replacing everything in the column with empty values.

Here are some other variations I have tried:

UPDATE ItemInformation
SET Description = REPLACE(ltrim(rtrim(Description)), ',', '')

UPDATE ItemInformation
SET Description = REPLACE(',%', ',', '')

UPDATE ItemInformation
SET Description = REPLACE(',', ',', '')
WHERE Description = '%,%'

None of these worked they way I wanted them to. What am I doing wrong?"

OMB
Yak Posting Veteran

88 Posts

Posted - 2004-01-19 : 10:00:30

try this

update iteminformation
set description = replace(description,',','')

OMB

Wyatt70
Starting Member

6 Posts

Posted - 2004-01-21 : 08:12:22

I tried it, and nothing happened. The values in the column are unchanged.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-21 : 08:20:07

Are you sure.
You had in incorrect where clause in one of your examples

update iteminformation
set description = replace(description,',','')
where description like '%,%'

try
select description
from iteminformation
where description like '%,%'

If that returns anything then the replace should update those rows.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-01-21 : 08:23:16

OMB's solution is correct. How are you executing the command, are you using QA or some other front-end? What I'm getting at is, is this part of a transaction that may need committed?

Raymond

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-21 : 11:42:05

He's right...it doesn't work


USE Northwind
GO

CREATE TABLE ItemInformation([Description] varchar(80))
GO

INSERT INTO ItemInformation([Description])
SELECT 'CHOCOLATE CHIP‚' UNION ALL
SELECT '‚COOKIES‚' UNION ALL
SELECT '‚CROISSANTS *PLAIN*‚' UNION ALL
SELECT '‚DONUTS‚' UNION ALL
SELECT '‚DONUTS *DOZEN*‚' UNION ALL
SELECT '‚MUFFINS‚' UNION ALL
SELECT '‚BAGELS‚' UNION ALL
SELECT '‚ROLLS‚' UNION ALL
SELECT '‚CUPCAKES‚' UNION ALL
SELECT '‚CRISPIES‚' UNION ALL
SELECT '‚DANISH/SWEET ROLLS‚' UNION ALL
SELECT '‚FUDGE BROWNIES‚' UNION ALL
SELECT '‚PUFF PASTRIES/ECCLES‚' UNION ALL
SELECT '‚STICKY BUNS‚' UNION ALL
SELECT '‚TURNOVERS‚' UNION ALL
SELECT '‚BLACK & WHITE COOKIES‚' UNION ALL
SELECT '‚LINZER TARTS‚' UNION ALL
SELECT '‚SCONES/BISCUITS‚' UNION ALL
SELECT '‚SCUFFINS‚' UNION ALL
SELECT '‚SINFULL BITS‚'
GO

SELECT * FROM ItemInformation
GO

UPDATE ItemInformation
   SET [Description] = REPLACE([Description],',','')
GO

SELECT * FROM ItemInformation
GO

DROP TABLE ItemInformation
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-21 : 11:45:07

This is BIZARRE!


SELECT REPLACE([Description],',','')
  FROM ItemInformation

SELECT REPLACE([Description],'C','')
  FROM ItemInformation

SELECT CHARINDEX(',',[Description])
  FROM ItemInformation

EDIT ...and more


DECLARE @x varchar(80)
SELECT @x = '‚COOKIES‚'
SELECT @x
SELECT REPLACE(@x,',','')

EDIT ..and more


INSERT INTO ItemInformation([Description])
SELECT 'CHOCOLATE, CHIP‚' UNION ALL
SELECT 'CHOCOLATE, CHIP‚' UNION ALL
SELECT ',CHOCOLATE, CHIP‚' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ ' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ A' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ , '
GO

SELECT REPLACE([Description],',','')
  FROM ItemInformation
GO

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-21 : 13:13:19

That's because that , in the table isn't the same as , in the replace command.
Somehow it's got an invalid character in there.
It's ascii 130 instead of ascii 44.

If you do the replace with char(130) instead of , it should work

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-21 : 13:46:34

Yeah....we just ran across that...

http://www.dbforums.com/showthread.php?postid=3607727#post3607727

How bizzare...


SELECT ASCII(SUBSTRING([Description],1,1)), ASCII(',')
FROM ItemInformation

EDIT: ...kinda like, When is a comma not a comma...when it's a comma...

funny

And Nigel, how did you think to track it down that way?

Just curious....didn't know there were two ACII values for a comma

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-22 : 06:28:06

The replace didn't work so I just looked at the ascii value for what it was trying to replace.
ascii only use half the values available so there's lot's of redundency and depends on the code pages you have installed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-01-22 : 07:26:16

It can also apply to single quotes....
'....and `
can visually look similiar under certain character sets........but have different ascii codes.

as I found out.....

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-01-22 : 08:34:05

It's not ASCII, it's Code Page 1252 (Windows Latin 1).
The answer to "when is a comma not a comma" is "when it's a SINGLE LOW-9 QUOTATION MARK". In Unicode, this character has code point U+201A. It's used (or used to be, at least) in German for open single quotes.

Oh, and U+0060 ` isn't a quote, it's a spacing GRAVE ACCENT.
And U+00B8 ¸ is a spacing CEDILLA.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-01-22 : 08:50:16

I know ` isn't a quote....I just said it can LOOK like a quote on certain character sets....because the 'slant' is reduced/absent....

Wyatt70
Starting Member

6 Posts

Posted - 2004-02-20 : 14:00:49

Yes, this is what happened. I received this data from a mainframe computer. What I didn't know was that the comma character from my keyboard (ASCII 44) was not the same as the comma character used by our mainframe. Another DBA in my company suggested I do a select statement on the column, then copy and paste the comma into my replace statement and try again. By copying and pasting the comma, we obtained the correct character to search for and it worked.

Thanks to all who responded. I see I wasn't the only one going crazy with this.

quote:
Originally posted by nr

That's because that , in the table isn't the same as , in the replace command.
Somehow it's got an invalid character in there.
It's ascii 130 instead of ascii 44.

If you do the replace with char(130) instead of , it should work

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-23 : 05:54:53

Glad to know my suggestion wasn't that mad either....

The key point to this problem....was the missing information "I received this data from a mainframe computer"......that's what caused my "`" "'" problem as well in the 1st place....data coming from another source doesn't necessarily behave the same as data from our own machines.

At least by this we've all learnt more.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources