CODEWITHSUNDEEP
sql-serverregext-sql
Harry Kane
Harry Kane

Reputation: 11

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

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!

Upvotes: 0

Views: 410

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 1

Related Questions