Regex expression in MS SQL

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
   开发者_StackOverflow                                         (reverse(opis)
                                                    ,0
                                                    ,charindex(
                                                                'C',reverse(opis)
                                                               )
                                            )
                                      )
                            ,'-',' ')
                          )
                  )
            ) as jci
from #tempTBL

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

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).

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

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