SQL to update multi-values?

June 18, 2007 4:43 PM

Need some help with MS SQL text replacement.

I have a DB table in MSSQL that has a value column that holds various types of data- single values or multi-values. the multi-values are pipe-delimited.

I have been given a set of replacement data with new values for ~900 pieces of data, which may appear in multiple rows in the table.

So the data is like:
ID value
1 12345
2 page3|2345|$123.45
3 123|first entry|BLANK|(user4)
4 2345

And my new data mapping is like:
NEW OLD
67890 12345
555 2345
09854 123

What I want to do is using SQL, make the replacement so that what I end up with is:

ID value
1 67890
2 page3|555|$123.45
3 09854|first entry|BLANK|(user4)
4 555

I know that I need to use either STUFF or REPLACE, but beyond that I'm stuck.

Thanks hive mind!

posted by Four Flavors to Computers & Internet (15 answers total)

Something like:

Update Table SET value = REPLACE(value, '12345', '67890') WHERE field1 like '%12345%'
posted by pompomtom at 5:08 PM on June 18, 2007

It'll be more complicated than that. I notice you don't want to replace the 123 in $123.45. Try this:

Surround the entire value with pipes. Then search for |pattern| and replace that. Then remove the pipes on the ends.

I don't have a MySQL database to test with here, but it would be something like this:

UPDATE table SET value = TRIM(BOTH '|' FROM REPLACE(CONCAT('|', value, '|'), '|12345|', '|67890|'))
posted by Khalad at 6:20 PM on June 18, 2007

Oops, MS SQL you say, not MySQL. Well, hopefully I got the idea across!
posted by Khalad at 6:21 PM on June 18, 2007

The only way I, personally, would ever be confident in this working as desired is to:

a) properly normalize the data into temporary tables
b) run your replacements
c) stuff all the data back where it originally was

Perhaps a hidden step:

d) revoke permissions from whomever designed this schema in the first place
posted by sbutler at 8:18 PM on June 18, 2007

This schema is brain-dead enough to make this essentially a text processing job, rather than a database job, and I think it would be best done with good text processing tools.

If that were my problem, and I had a way to dump the table I wanted to process into a flat text file, and a way to re-import a suitably formatted flat text file into a database table again, I'd do that, and then do the replacements with sed: first, use sed to generate a file full of sed substitute commands (like s/|from|/|to|/), then top that with a substitute command that surrounds your whole value field with | delimiters, and tail it with one that strips the outer delimiters back off that field.

If you're interested in pursuing that approach but you don't know sed, toss me a couple of files (a table dump and a mapping file) and I'll write you a sed script that applies the mapping to the table dump.
posted by flabdablet at 8:57 PM on June 18, 2007

Khalad... while I agree with your plan of using the pipes, my update wouldn't have touched the 123.45.

So, change that entry to:



Update Table SET value = REPLACE(value, '|12345|', '|67890|') WHERE field1 like '%|12345|%'


...and then sbutler's part d.
posted by pompomtom at 8:59 PM on June 18, 2007

Does REPLACE() replace all occurrences, or only the first one?
posted by flabdablet at 1:47 AM on June 19, 2007

Response by poster: Pompomtom- do I need to create that update statement for every row in the new_data_map table?

Yes, I know that the schema is retarded. Ironically we have a 'sister' table that takes the same design and actually normalizes it so that each value is on a row, but sadly it's only for a subset of the id's. For that table I can simply update the value to the new value for each row in the new_data_map.
posted by Four Flavors at 9:28 AM on June 19, 2007

Yes, you need to do it for each row in new_data_map. For your sample map:

UPDATE table SET value = TRIM(BOTH '|' FROM REPLACE(CONCAT('|', value, '|'), '|12345|', '|67890|'))
UPDATE table SET value = TRIM(BOTH '|' FROM REPLACE(CONCAT('|', value, '|'), '|2345|', '|555|'))
UPDATE table SET value = TRIM(BOTH '|' FROM REPLACE(CONCAT('|', value, '|'), '|123|', '|09854|'))

Notes about pompomtom's query: (1) The WHERE clause is unnecessary. (2) If you don't add pipes before the REPLACE and then remove them after the REPLACE won't match the first and last values in a row. That's why I have the CONCAT and TRIM in there.
posted by Khalad at 10:26 AM on June 19, 2007

Response by poster: BOTH isn't a keyword in MSSQL. I think I understand everything that you're doing except for that.
posted by Four Flavors at 5:19 PM on June 19, 2007

Hm, try without it then. TRIM('|' FROM ...)
posted by Khalad at 5:56 PM on June 19, 2007

Response by poster: Nope, seems you you'd need a select or something to go with the FROM clause.
posted by Four Flavors at 9:09 AM on June 20, 2007

Alrighty, apparently MS SQL doesn't support the TRIM function. Try this...

UPDATE table SET value = REPLACE(CONCAT('|', value, '|'), '|12345|', '|67890|')
UPDATE table SET value = SUBSTRING(value, 2, LEN(value) - 2) WHERE value LIKE '|%|'
UPDATE table SET value = REPLACE(CONCAT('|', value, '|'), '|2345|', '|555|')
UPDATE table SET value = SUBSTRING(value, 2, LEN(value) - 2) WHERE value LIKE '|%|'
UPDATE table SET value = REPLACE(CONCAT('|', value, '|'), '|123|', '|09854|')
UPDATE table SET value = SUBSTRING(value, 2, LEN(value) - 2) WHERE value LIKE '|%|'
etc.
posted by Khalad at 1:05 PM on June 20, 2007

Response by poster: CONCAT isn't supported, but + is, I changed it to:
UPDATE table SET value = REPLACE(('|'+ value+ '|'), '|12345|', '|67890|')


The risk I see with this strategy is that some values may already have pipes at the beginning and end, and those should be left on. I think I figured out a way to do it:
UPDATE table SET value = REPLACE(('|'+ value+ '|'), '|12345|', '|67890|') where value like '%12345%'
UPDATE table SET value = SUBSTRING(value, 2, LEN(value) - 2) WHERE value LIKE '|%|' and value like '%|67890|%'

Does that make sense? All of the test I've run seem to indicate that it'll work (the rows with pipes on either end aren't disturbed)
posted by Four Flavors at 2:33 PM on June 20, 2007

Yeah, that should work.
posted by Khalad at 7:09 PM on June 20, 2007