Create table MessageDetail ( Msg varchar(1000) ) End space,?,/>, ", end of line Insert into MessageDetail Values ('abc1 just'), ('This abc5? is abc2 just testing abc3 infomation'), ('This is just xyz1 testing infomation abc234/> test'), ('This is just xyz34 testing abc231?3 infomation'), ('This is just testing xyz67/> 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)> 0 Or PATINDEX('%xyz%', Msg)> 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.
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. :
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> 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 `>` ]