CODEWITHSUNDEEP
mysqlsqldatabasestring
Cyri1
Cyri1

Reputation: 427

Mysql : How can i replace a part of a string?

I want to delete everything after "x" in the following urls :

i have :

url
/product/dtphdmi230rx?subtype=384
/product/dtphdmi230tx?subtype=385
/product/dtphdmi330rx?subtype=386
/product/dtphdmi330tx?subtype=387

i want :

url
/product/dtphdmi230rx
/product/dtphdmi230tx
/product/dtphdmi330rx
/product/dtphdmi330tx

I know it's easy with mysql 8.0 with regex_replace but i can't update my server. Is there any way with mysql 5 ?

nb : There is always a "?" in urls, it can be the first character to delete.

Thanks for help

Upvotes: 0

Views: 48

Answers (1)

GMB
GMB

Reputation: 222672

Just:

left(url, locate('x?', url))

Demo on DB Fiddle:

with mytable as (
    select '/product/dtphdmi230rx?subtype=384' url
    union all select '/product/dtphdmi230tx?subtype=385'
    union all select '/product/dtphdmi330rx?subtype=386'
    union all select '/product/dtphdmi330tx?subtype=387'
)
select left(url, locate('x?', url)) from mytable

| left(url, locate('x?', url)) |
| :--------------------------- |
| /product/dtphdmi230rx        |
| /product/dtphdmi230tx        |
| /product/dtphdmi330rx        |
| /product/dtphdmi330tx        |

Note: as commented by Raymond Nijland, if the ? occurs just once in the string and can be used as the first character to remove, you can also do:

left(url, locate('?', url) - 1)

Upvotes: 3

Related Questions