1

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

  • 1
    `/pattern/` is a PERL style regex. In Oracle we use simple string as a pattern without additional `/.../`. That's why you do not need to escape them. You may check [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=b20f523c4407ef51b2d447c28bd72988) – astentx Jan 13 '22 at 20:33
  • I cannot reproduce, this fiddle shows the matched text: http://sqlfiddle.com/#!4/c03cf/1 – knittl Jan 13 '22 at 20:33

1 Answers1

2

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
micah
  • 838
  • 7
  • 21