REGEXP_SUBSTR with a URL-CodePudding

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

CodePudding user response：

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