Menu
I have data in a column that is causing problems. There are multiple bad characters I need to remove. I'd like to do this in the query.
On this question: MySQL string replace
I see where I can SELECT REPLACE(string_column, 'search', 'replace') as url but this only works for example replacing a / with //
I need to replace / with // and also & with && for example in a single query. What is the best way to achieve this?
542
Using the REPLACE() function will allow you to change a single character or multiple values within a string, whether working to SELECT or UPDATE data.
SELECT REPLACE(REPLACE(REPLACE(REPLACE('3*[4+5]/{6-8}', '[', '('), ']', ')'), '{', '('), '}', ')'); We can see that the REPLACE function is nested and it is called multiple times to replace the corresponding string as per the defined positional values within the SQL REPLACE function.
MySQL REPLACE() FunctionThe REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: This function performs a case-sensitive replacement.
If you are replacing multiple character then you need to use multiple replace in one query something as below. But if there are many characters to be replaced then its better to use application layer to handle it. In other words for few replacement its easy to use query but for many character replacement the query really becomes messy and ends up hard to read or change.
select
replace(
replace(string_column,'/','//'),'&','&&'
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
