Regex expression 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

Ed Harper · Accepted Answer · 2010-03-10 11:58:56Z

1

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

answered Mar 10, 2010 at 11:58

Ed Harper

21.6k5 gold badges60 silver badges85 bronze badges

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

Comments

particle · Accepted Answer · 2010-03-10 11:57:22Z

1

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

answered Mar 10, 2010 at 11:57

particle

3,3604 gold badges29 silver badges41 bronze badges

Comments

VladV · Accepted Answer · 2010-03-26 14:40:20Z

1

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

edited Mar 26, 2010 at 14:40

answered Mar 11, 2010 at 15:05

VladV

10.5k3 gold badges36 silver badges48 bronze badges

Collectives™ on Stack Overflow

Regex expression in MS SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related