CODEWITHSUNDEEP
sql-serverregex
Subha
Subha

Reputation: 1051

How to get part of string that matches with regular expression in SQL Server

I am having a requirement where I need to parse a column value to get a work request number associated with it. For example, the column value may contain - "Worked on AB-0012589" or "AB-0012589 is completed" or whatever containing the work request number in it. (Here AB-0012589 is the work request number).

Format of the work request number will not change. It will be in xx-xxxxxxx format, where two characters before the hyphen will be letters and the later part will be numbers. How can I parse the work request number from the column value using regular expression?

So, if the column value is "Completed AC-0015587" my desired output is AC-0015587

Thanks in advance!

Upvotes: 5

Views: 12429

Answers (5)

Nina
Nina

Reputation: 1075

This works:

declare @Value nvarchar(4000)
set @Value='Worked a-232 on AB-0012589'

select substring(@Value,
patindex('%[A-Z][A-Z][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@Value),10)

Replace @Value with your column

Output: AB-0012589

Upvotes: 7

cloudsafe
cloudsafe

Reputation: 2504

Create function ReturnPart (@val as varchar(100))
 Returns varchar(10)
 as
 begin
 declare @length as int 
 declare @loop as int
 declare @ret as varchar(10)
 set @loop =1
 set @length = len(@val)

 while @loop<=@length-9
    begin
    if exists(select 1 where substring(@val,@loop,10) like '[a-z][a-z][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
        set @ret = substring(@val,@loop,10)
    set @loop=@loop+1
    end
Return @ret
end



select dbo.ReturnPart('Worked on AB-0012589')
select dbo.ReturnPart('AB-0012589 is completed')

Upvotes: 4

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Use patindex and find '-' position from that we can fetch work request number 

select substring('Completed AC-0015587',
          patindex('%-%','Completed AC-0015587')-2,len('Completed AC-0015587'))

Update: Change patindex like below it may help you

select substring('Worked on- AV-0217585',
patindex('%-[0-9]%','Worked on- AV-0217585')-2,len('Worked on- AV-0217585'))

Upvotes: 1

Chanukya
Chanukya

Reputation: 5893

 select substring('Completed AC-0015587',(CHARINDEX('-','Completed AC-0015587')-2), 10)

ouput AC-0015587

Upvotes: 1

Viki888
Viki888

Reputation: 2774

You can try using below expression

substring(t1.column_value,(CHARINDEX('-',t1.column_value)-2), 10)

Using above gave me below result

enter image description here

Hope this should help you out.

Upvotes: 1

Related Questions