parsing a column to replace all commas with spaces

Question

parsing a column to replace all commas with spaces

scotttr

SSC Eights!

Points: 965
More actions
August 26, 2002 at 9:30 am

#158085

I have a table where the last and first name are in one column with a comma separating them EX:(lastname, firstname). I need to parse and update all values in this column in my table and replace the commas with spaces.

Viewing 8 posts - 1 through 7 (of 7 total)

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

katravax Old Hand Points: 353 More actions · Answer 1

UPDATE mytable SET fullname = REPLACE(fullname, ',', ' ')

- Troy King

scotttr SSC Eights! Points: 965 More actions · Answer 2

Cool, that works on a single column. Now here's the fun part. I need to parse every column in my table to update commas with spaces. I would prefer to do it using a looping statement assigning each column name to a variable with a single update statement as opposed to using multiple update statements

scotttr SSC Eights! Points: 965 More actions · Answer 3

Cool, that works on a single column. Now here's the fun part. I need to parse every column in my table to update commas with spaces. I would prefer to do it using a looping statement assigning each column name to a variable with a single update statement as opposed to using multiple update statements

Antares686 SSC Guru Points: 125444 More actions · Answer 4

This should get it done.

--Should be able to change YourTableNameHere with your table name.

DECLARE @maxcol int

DECLARE @posmark int

DECLARE @col VARCHAR(255)

SET @posmark = 0

SET @maxcol = (select max([colid]) from syscolumns WHERE [id] = OBJECT_ID('YourTableNameHere'))

WHILE @posmark < @maxcol

BEGIN

SET @posmark = @posmark + 1

--If this is SQL 7 SP3 or lower you need to check for colid found in loop as there may not a sequential list.

SET @col = (SELECT [name] FROM syscolumns WHERE colid = @posmark AND [id] = OBJECT_ID('YourTableNameHere'))

EXECUTE ('UPDATE YourTableNameHere SET [' + @col + '] = REPLACE([' + @col + '], '','', '' '')')

END

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

katravax Old Hand Points: 353 More actions · Answer 5

(This was in reply to scotttr, not Antares686).

That sounds like too much hassle to me. Why not just do this:



UPDATE mytable

SET    col1 = REPLACE(col1, ',', ' ')

     , col2 = REPLACE(col2, ',', ' ')

     , col3 = REPLACE(col3, ',', ' ')

     , col4 = REPLACE(col4, ',', ' ')

Otherwise, you'll wind up writing a script to hit syscolumns to populate your column variable, then planting that variable in an update string, then EXECing the string. It's much easier to do it manually 🙂

- Troy King

Edited by - katravax on 08/26/2002 10:26:08 AM

- Troy King

katravax Old Hand Points: 353 More actions · Answer 6

Antares, you beat me to it. I was going to post this. It outputs the code to run rather than execing it directly so he can review it before running it. I'd normally use the direct exec method myself.



DECLARE @table varchar(100)

SELECT  @table = 'MYTABLE' --<------- change this



DECLARE @col varchar(100)

SELECT  @col = ''



WHILE @col IS NOT NULL BEGIN

   SELECT @col = MIN(name) FROM syscolumns 

     WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND name = @table) 

     AND name > @col

   IF @col IS NULL BREAK

   PRINT 'UPDATE mytable SET ' + @col + ' = REPLACE(' + @col + ', '','', '' '')'

END

- Troy King

scotttr SSC Eights! Points: 965 More actions · Answer 7

Thanks guys, Antares in particular. Us geeks gotta stick together.

Peace out