Regex expresion in MS SQL

Question

I do not not know much about Regex, I want to try parsing sting from database according to flowing instructions. I know that I am need to use CLR but for begin I want to learn Regex

Data in tables look like

create table #tempTBL (opis varchar(40))
go
insert into #tempTBL
select 'C 136'
union 
select 'C 145'
union 
select 'C146'
union 
select 'AK C 182'
union 
select 'C  277'
union 
select 'C-240'
union 
select 'ISPRAVKA PO C 241'

And select sting looks like

Select 
     reverse(
            rtrim(
                    ltrim(
                            replace(
                                    (substring
                                            (reverse(opis)
                                                    ,0
                                                    ,charindex(
                                                                'C',reverse(opis)
                                                               )
                                            )
                                      )
                            ,'-',' ')
                          )
                  )
            ) as jci
from #tempTBL

How should looks like my C# code to I repeat this using regex

Answer 1

See this article. It simply add a .NET function to sqlserver and than call it as sqlserver function in your query.

Answer 2

Rather than building a CLR assembly to apply a regex to meet this specific requirement, you might be better off building a set of general-purpose regex CLR functions.

There are many examples out there, but this article is as good a place to start as any

EDIT If your example covers the full range of possible configurations of your data, the regex you need may be something as simple as (\d+) (meaning one or more consecutive digits returned as a match group).

Answer 3

Using CLR regex could bу rather slow. If the transformation is as easy as in the example, you'd better go with simple SQL.

Have a look at PATINDEX function, it might be useful. Also, I'd suggest to encapsulate the extraction into a UDF for clarity.

create function dbo.ParseNum(@s varchar(40)) returns char(3)
as begin
    declare @n int
    set @s = replace(replace(@s, '-', ''), ' ', '')
    set @n = patindex('%C[0-9][0-9][0-9]', @s)
    if @n = 0 return null
    return substring(@s, @n+1, 3)
end
go

select opis, dbo.ParseNum(opis) from #tempTBL