Replace Statement In Table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Replace Statement In Table

Author

Topic

dalvarado
Starting Member

4 Posts

Posted - 2010-01-18 : 14:54:02

I have a table with BOX_PREFIX, BOX_NO, BOX_ID, COMMENTS fields. In the box_id field there are entries such as 'XY 123' AND 'XY123'. but not always 123.

How do I replace all the 'XY123' with 'XY 123' where I do not know what the ending numbers are?

Thanks,
David

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-18 : 15:25:45

[code]update yourTable
SET someField = Replace(someField, 'XY', 'XY ')[/code]

dalvarado
Starting Member

4 Posts

Posted - 2010-01-19 : 00:24:36

Thank you this worked great. I updated all xy space to xy and then all xy to xy space for reassurance.

Thank you!!!

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 02:36:49

Just FYI you can do that in one strike (maybe you did?!):


update	yourTable
SET	someField = Replace(Replace(someField, 'XY ', 'XY'), 'XY', 'XY ')

perhaps add

WHERE someField LIKE '%XY[^ ]%'

to only target rows that need updating.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources