CODEWITHSUNDEEP
phpmysqlreplace
Liviu ZeJah
Liviu ZeJah

Reputation: 113

Find a Slash in a string

In my MySQL database I have a lot of product codes in this form :

COD1234/ABC123/XXX0293

What I want to do is change that to :

COD1234 / ABC123 / XXX0293

The problem is I already have some of the codes in the correct form

So, I need to replace the slash ( / ), with [space]/[space] ... and of course, if there is more then one space, it's a problem ..

... and I know I should do this with PHP but if someone knows a Mysql way of doing this it would be really nice !

Upvotes: 0

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Here is one approach, phrased as a select:

select replace(replace(ProductCodes, '/', ' / '), '  /  ', ' / ')

The query string to replace for the outer replace has two spaces rather than one.

If you want an update, then this might work:

update x
    set ProductCode = replace(ProductCode, '/', ' / ')
    where ProductCode like '%/%' and ProductCode not like '% / %'

This assumes that all the slashes have spaces or none.

Upvotes: 1

Don Dickinson
Don Dickinson

Reputation: 6258

you could do it in two steps as long as you never want double spaces ..

product_field = replace(product_field, '/', ' / ')
product_field = replace(product_field, '  ', ' ')

Upvotes: 1

Related Questions