Extracting certain pattern of string from rows

Question

Create table MessageDetail
(
Msg varchar(1000)
)

End space,?,/&gt;, &quot;, end of line

Insert into MessageDetail Values
('abc1 just'),
('This abc5? is abc2 just testing abc3 infomation'),
('This is just xyz1 testing infomation abc234/&gt; test'),
('This is just xyz34 testing abc231?3 infomation'),
('This is just testing xyz67/&gt; infomation abc32? test'),
('This xyz34 is xyz5g55 just xyz5 infomation'),
('This is just testing infomation xyz'),
('This is xyz56f3 just testing abc')

select * from MessageDetail

Where PATINDEX('%abc%', Msg)&gt; 0 
Or PATINDEX('%xyz%', Msg)&gt; 0

on above table, I need to extract only string from those rows of pattern which start from abc or xyz Those patter may be 1 or more time. If more then one then have to retrive rest of those pattern too. Start with same pattern as in where clause but end of that links are some time ", some time space, some time /> and some time end of line.

Answer 1

0

If you are running SQL Server 2005 and above, then the easies way will be CLR Implementation of RegEx.

You can take a look on my post SQL Server 2005 and SQL Server 2008 Regular Expressions and pattern matching.

There is also a sample solution available for download and this is easily solvable by RegEx expression.

answered Jun 20 '12 at 01:37 PM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

+1. Cannot agree more. Best solution in such scenarios is to use a CLR function.

Jun 20 '12 at 01:44 PM Usman Butt

to use CLR I need to know VB and C# too. Or I can just use ms-sql?

Jun 20 '12 at 02:01 PM munabhai

@munabhai Yes, you need to know VB and/or C#. However, in this case, because @Pavel's solution (in the post linked in his answer above) is complete and includes very detailed instructions on how to deploy the CLR solution to the SQL Server, all you really need is just a basic understanding of the syntax. Read through the article, follow the code along with instructions and you should be able to deploy his solution in the relatively short period of time. SQL Server installation out of the box includes so called BIDS (Business Intelligence Development Studio), which you can use as an IDE. Open it, start from creating a new SQL-CLR project and go from there. If you already have the the full blown version of Visual Studio then the CLR project template is not going to be in BIDS, but will be under Database Projects folder in VS. If you cannot find BIDS on the box where SQL Server is installed then you can re-run the SQL Server installation from the disk and opt to include the components. This will install BIDS.

Jun 20 '12 at 03:58 PM Oleg

(comments are locked)

Answer 2

I agree with Pavel with a caveat. If you need to do this inside the database engine then CLR is by far the best way to go, and his post that he linked is an excellent reference. Phil Factor also has a good post on this topic at: CLR Assembly RegEx Functions for SQL Server by Example

However, I think you would generally be better off doing it outside of the databse, in the application or presentation layers. You can use SQL's limited pattern matching with LIKE to determine what should be sent to the application or presentation layers, and then use RegEx there.

If you truly must do this in T-SQL, you certainly can, but I can't think of a way to do it in one query. I would use an initial select with a LIKE conditional in the where clause to determine which rows needed to be processed and then put them in a table variable or temp table. Then I would use nested loops to process each row and extract each match within the row. It wouldn't be particularly hard, but it would be awkward and ineffecient compared to using RegEx either in CLR or in your application layer.

Answer 3

0

Just for fun (and yes just because @TimothyAWiseman said he couldn't think of a way of doing this in one query) here's a single query, pure t-sql solution. Not saying it will be fast though!

;with tally as
(select top 10000
    ROW_NUMBER() over(order by sv1.number) N
from
    master.dbo.spt_values sv1,
    master.dbo.spt_values sv2
),

AlteredMsgs as 
(select row_number()over(order by msg) as ID, 
   cast(' '+msg+ ' ' as char(1002)) as Msg
from MessageDetail)
,

stringstofind (pattern) as 
(select 'xyz' union select 'abc')


select
 substring(msg,N+1, charindex(' ',msg,N+1)-N-1)
from AlteredMsgs
join tally on N&gt; len(msg)
join stringstofind
  on substring(msg,N+1, charindex(' ',msg,N+1)-N-1) like stringstofind.pattern+'%'
and substring(msg,N,1) = ' '
order by ID

[please note the code formatter is currently encoding '>' as > ]

answered Jun 21 '12 at 10:24 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

Ok, I'm impressed.

Jun 22 '12 at 03:48 PM TimothyAWiseman

Ha Ha - Thank you @TimothyAWiseman!

Jun 22 '12 at 04:00 PM Kev Riley ♦♦

(comments are locked)

Answer 4

0

Thanks Pavan, Oleg, TimothyAwiseman for suggestion. I am planning to use loop to check if there is multiple occurance of my patter.

answered Jun 20 '12 at 04:48 PM

munabhai
0 ● 1 ● 1 ● 3

(comments are locked)

Answer 5

0

Kev you are awesome

answered Jun 21 '12 at 01:53 PM

munabhai
0 ● 1 ● 1 ● 3

(comments are locked)

Extracting certain pattern of string from rows

Follow this question

Related Questions