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

Question

我想删除以下网址中“x”之后的所有内容:

我有:

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

我想要:

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

我知道使用带有 regex_replace 的 mysql 8.0 很容易，但我无法更新我的服务器。 mysql 5 有什么办法吗？

nb:总有一个“？”在 url 中，它可以是要删除的第一个字符。

感谢帮助

Answer 1

只是:

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)