How to extract a pattern string from another using a REGEX in SQL Server?

Question

Is it possible in SQL Server to extract a string with a REGEX?

Something like REGEXP_SUBSTR() ?

I know with PATINDEX() I can use a REGEX to find the initial position, but I want it to extract directly the string that matches the pattern in the REGEX, does it exist in SQL Server?

Let's assume I have a message string like

@message = 
'- Tomorrow: 75 degrees (bla xyz)  
- Day 1: 78 degrees etc 3300 fx  
- Day 2: 76 degrees ppp 99  
- Day 3: 80 degrees xxx2 '

I need to extract in multiple string variables:

@tomorrow_temperature = '75'
@day_1_temperature '78'
@day_2_temperature '76'
@day_3_temperature '80'

How can I use REGEX to extract just the number associated to each day? Is there any function that given a REGEX extract automatically the matching string

I tried using PATINDEX() but that of course extracts just the starting position of the string, it doesn't tell me how long it is and I don't want to use fixed amount of characters, just extract the whole string and from that extract the number of degrees

SELECT PATINDEX('%Day 1:%degrees', @message) Day_1;

I edited the example after Johns solution (my bad I made the example slightly different from reality), your solution applies perfectly to the previous example that I copy here in case anybody will need it in the future.

@message = 
'- Tomorrow: 75 degrees
 - Day 1: 78 degrees
 - Day 2: 76 degrees
 - Day 3: 80 degrees'

Thank you John!

John Cappelletti · Accepted Answer · 2023-01-27 06:24:47Z

1

Just because it seemed like a fun diversion

Example

Declare @message varchar(max) = '- Tomorrow: 75 degrees (bla xyz)  
- Day 1: 78 degrees etc 3300 fx  
- Day 2: 76 degrees ppp 99  
- Day 3: 80 degrees xxx2 '

Declare @tomorrow_temperature varchar(10)
       ,@day_1_temperature varchar(10)
       ,@day_2_temperature varchar(10)
       ,@day_3_temperature varchar(10)

;with cte as (
Select Item  = JSON_VALUE(JS,'$[0]')
      ,Value = JSON_VALUE(JS,'$[1]')
 From  OpenJSON( '["'+replace(string_escape(@message,'json'),'degrees','","')+'"]' ) A
 Cross Apply (values ('["'+replace(string_escape(Value,'json'),':','","')+'"]') ) B(JS)
)
Select  @tomorrow_temperature = max(case when item like '%tomorrow%' then value end)
       ,@day_1_temperature    = max(case when item like '%day 1%' then value end)
       ,@day_2_temperature    = max(case when item like '%day 2%' then value end)
       ,@day_3_temperature    = max(case when item like '%day 3%' then value end)
 From  cte

Straight Text way

Declare @message varchar(max) = '- Tomorrow: 75 degrees (bla xyz)  
- Day 1: 78 degrees etc 3300 fx  
- Day 2: 76 degrees ppp 99  
- Day 3: 80 degrees xxx2 '

Declare @tomorrow_temperature varchar(10) = trim(substring(@message,charindex('Tomorrow',@message)+10,3))
       ,@day_1_temperature    varchar(10) = trim(substring(@message,charindex('Day 1'   ,@message)+7 ,3))
       ,@day_2_temperature    varchar(10) = trim(substring(@message,charindex('Day 2'   ,@message)+7 ,3))
       ,@day_3_temperature    varchar(10) = trim(substring(@message,charindex('Day 3'   ,@message)+7 ,3))

Select  @tomorrow_temperature
       ,@day_1_temperature
       ,@day_2_temperature
       ,@day_3_temperature

edited Jan 27, 2023 at 6:24

answered Jan 27, 2023 at 5:46

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Harry Kane Over a year ago

Different from what I was expecting, but great solution! Thank you very much John, you are a bomb (:

John Cappelletti Over a year ago

@HarryKane As you have figured out by now, SQL Server does not support REGEX . Seems like a missed opportunity

Harry Kane Over a year ago

I really have no words, I saw we can use REGEX in the WHERE clause but not a function to extract substrings using REGEX? For real it doesn't exists? Seems too lame to be true You gave me an incredible solution to be honest, but let me take advantage one second more of your skills (if you have 5 more minutes otherwise thanks anyway).. how would you have extracted it singularly using PATINDEX and SUBSTRING using each single variable assignment?

Harry Kane Over a year ago

Shut, I think I made a wrong example in the @message source tho.. Correct is:

@message =  '- Tomorrow: 75 degrees (bla xyz)  - Day 1: 78 degrees etc 3300 fx  - Day 2: 76 degrees ppp 99  - Day 3: 80 degrees xxx2 '

Harry Kane Over a year ago

Still I am shocked you had to use all that witchcraft and MSSQL doesn't support string extraction by REGEX.. #_# Thanks again John! You da beast!

|

Collectives™ on Stack Overflow

How to extract a pattern string from another using a REGEX in SQL Server?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related