Need to narrow down data from a table which matches certain pattern.

Question

Post reply

Need to narrow down data from a table which matches certain pattern.

krishnamurali2489

Mr or Mrs. 500

Points: 512
More actions
April 19, 2016 at 3:55 pm

#310044

Hi All,

I have a situation where i need to pull the data from table which matches a certain String.

The table name is Audit, It has a column named detail.

The column detail has data like below

<xml><detail>The RetPlan table is being updated.</detail><sql>EXEC pUpdateRetPlanDet '0130438580', '2014-10-01', 'Z', 1, '2014-10-01', '2015-10-01', '2014-10-01', '2015-10-01',NULL,92,NULL,NULL,null,'N','A','2014-10-01',NULL,NULL,'N','99','09',1,2,''</sql></xml>

<xml><detail>The following queue record is being completed. account: 0130438580, plan term: 2013-10-01, plan period: 2013-10-01, eval date: 2015-04-01,rev date: 2015-04-01</detail></xml>

I need to pull all the records from this table which has a string pattern ,2,followed by any number from 1 to 9

Example pattern : ,2,1

The Steps i tried to pull the records is below :

based on the above example of data i gave, i found that number 2 often comes in 248 position so i gave a query like below and then decided to manually check from the returned records for the pattern.

select Detail,SUBSTRING(Detail,249,249) from Audit

but the row came did not have the pattern i was expecting.

I am new to SQL, so i am not sure whether any easy way or any built in function available for this scenario.

Please share some ideas on retrieving rows which matches ,2,followed by any number in detail column.

Thanks in Advance !

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Hendricks Ten Centuries Points: 1006 More actions · Answer 1

Steve Hendricks

Ten Centuries

Points: 1006

April 19, 2016 at 4:25 pm

#1873190

select *

from Audit

where detail like '%2%[0-9]%'

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 2

If you want it to be exactly a two, preceded by a comma, followed by a comma, followed by a number, then you'll want something like this:



CREATE TABLE #strings (some_string varchar(max));

INSERT INTO #strings VALUES

('blah blah, something,2,something'),

('more blah, some bleh,2,3,hmmm?'),

('What''s all this? Let''s count down by two, 8,6,4,2,0!'),

('This isn''t a string, that I would 2 like to have returned 8!');

--If you mean what you said about the numbers 1 through 9

SELECT *

FROM   #strings

WHERE  some_string LIKE '%,2,[1-9]%';

--Or if you actually mean ANY number, as you said later

SELECT *

FROM   #strings

WHERE  some_string LIKE '%,2,[0-9]%';

--I don't think the other provided option

-- will work for your requirements

--since it doesn't care about the commas

SELECT *

FROM   #strings

WHERE  some_string LIKE '%2%[0-9]%';