SQL "replace" query help needed

[MacMorag]

Hi guys,

I am trying to do the following (but having no luck). Does anyone know the query to do this:


I have one column where every record is in the format "abcd/123xxx456"

I want to replace the record so that it looks like "abcd/xxx/123xxx456"

xxx is different for every record


Why does this not work?

UPDATE columnA SET fieldB = REPLACE(fieldB, 'abcd/123*456'', 'abcd/*/123*456');


I'm guessing that you can't use the wildcard to extract and hold the value of xxx for each record. Is there another way to do this?

Any assistance is much appreciated.

Thanks :)

[Lats]

Give this a spin...
UPDATE
tablename
SET
fieldb = concat(left(fieldb,5),mid(fieldb,9,3),right(fieldb ,9))
WHERE
fieldb LIKE 'abcd/123'

[MacMorag]

Thanks Lats. Worked a treat - except that the second "/" was missing, but that was easily sorted.

:)

[Lats]

That's the problem when you don't have any data to test with :)

I trust you changed it to...
UPDATE
tablename
SET
fieldb = concat(left(fieldb,5),mid(fieldb,9,3),right(fieldb ,10))
WHERE
fieldb LIKE 'abcd/123'

[MacMorag]

Lats, I did it the long way round - as I'd already overwritten the original field (I just did a count and added the "/" in the relevant place). Otherwise I'd have needed to restore the database, and it's getting a wee bit on the big side now. But, yes, had I restored I would have done exactly as you suggest in #4.

Thanks again. :)