How to delete space inside the string under condition, using SQL?

I am looking for your support.
I am trying to find and replace part of string in URLs in the mySQL DB.

I have over 550 URLs inputted in the code, where I need to delete the spaces.

i.e.:

https://www.poczytaj.pl/ksiazka/historia-national-geographis-splendor-bizancjum, 317145?pp=5

https://www.poczytaj.pl/ksiazka/stara-akademia-platona-w-poczatkach-epoki-hellenistycznej-ostatni-okres-bogdan-dembinski, 413137?pp=5

Can you please advice what code I should ran?

My proposal which do not works:

UPDATE `h15bpt4rj_posts`
SET `post_content` = replace(post_content, ', ', ',')
WHERE 'post_content' LIKE ', ______?pp=5';


Underscore is exchanging six digits, which are inside the URL.
Can you help me to get URLs with SQL like:
href="https://www.poczytaj.pl/ksiazka/historia-national-geographis-splendor-bizancjum**,317145**?pp=5"
https://www.poczytaj.pl/ksiazka/stara-akademia-platona-w-poczatkach-epoki-hellenistycznej-ostatni-okres-bogdan-dembinski,413137?pp=5

Thanks

How is your code not working?
– Gordon Linoff
Jun 29 at 11:51

Is not changing any records in DB
– cosiek
Jun 29 at 19:52

3 Answers
3

Don't put single quotes around column names!

UPDATE h15bpt4rj_posts
SET post_content = replace(post_content, ', ', ',')
WHERE post_content LIKE '%, ______?pp=5';


Only use single quotes for string and date constants.

You also need a wildcard at the beginning of the LIKE pattern. Unlike regular expressions, LIKE applies to the whole string. You could also use:

LIKE

LIKE

WHERE post_content REGEXP ', [0-9]{6}[?]pp=5$';


Thank you for your answer.

Can you please signify, how the full code should like?
Once running:

UPDATE h15bpt4rj_posts
SET post_content = replace(post_content, ', ', ',')
WHERE post_content LIKE '%, ______?pp=5';

UPDATE h15bpt4rj_posts
SET post_content = replace(post_content, ', ', ',')
WHERE post_content LIKE '%, ______?pp=5'
WHERE post_content REGEXP ', [0-9]{6}[?]pp=5$';

and

UPDATE h15bpt4rj_posts
SET post_content = replace(post_content, ', ', ',')
WHERE post_content REGEXP ', [0-9]{6}[?]pp=5$';

do not work. Sorry for plain questions, but this is my first time with changes in DB.

Thank you.

I got the solution.

UPDATE h15bpt4rj_posts
SET post_content = replace(post_content, ', ', ',')
WHERE post_content REGEXP ', [0-9]{6}[?]pp=5';

Thanks!

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.