I have a string in which I’m trying to extract a URL from. When I run it on this RegEx site, it works fine.
The Regex Pattern is: http:\/\/GNTXN.US\/\S+
The message I’m extracting from is below, and lives in a column called body in my SQL database.
Test Message: We want to hear from you! Take our 2022 survey & tell us what matters most to you this year: http://GNTXN.US/qsx Text STOP 2 stop/HELP 4 help
But when I run the following in SQL:
SELECT
body,
REGEXP_SUBSTR(body, 'http:\/\/GNTXN.US\/\S+') new_body
FROM
table.test
It returns no value. I have to imagine it’s something to do with the backslashes in the URL, but I’ve tried everything.
The new_body output should read as http://GNTXN.US/qsx
>Solution :
In mysql you just need to escape the \
select body, REGEXP_SUBSTR(body, 'http:\\/\\/GNTXN.US\\/\\S+') as new_body
from table.test;
new_body output:
http://GNTXN.US/qsx