T SQL search instring

I have a field containing a coded string, part of which appears like this (there is other string data in the field either side of the below examples)...

foo: 0,0,3,0;
bar: 0,0,2,0;
foo: 0,2,1,0;
bar: 0,0,0,0;
foo: 1,2,3,1;
bar: 0,1,8,0;

What I need to do is select all rows that contain a value greater than 2 in the 3rd of the 4 comma-separated numbers. In other words, to return only the following rows from the above example...

foo: 0,0,3,0;
bar: 0,0,2,0;
foo: 1,2,3,1;
bar: 0,1,8,0;

How would the query be constructed to achieve this?

I'm thinking along the lines of...

select * from mytable where
(myfield like '%foo: [0-10],[0-10],[3-10],[0-10];%') OR
(myfield like '%bar: [0-10],[0-10],[3-10],[0-10];%')


...however this is not returning the desired results i.e. it returns values that contain a 0 in the 3rd part of the comma separated number string.
drl1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
TheAvengerConnect With a Mentor Commented:
The groups for numbers should be like this:

[0-9]

This means all digits from 0 to 9. So your query would be:

select * from mytable where
(myfield like '%foo: [0-9],[0-9],[3-9],[0-9];%') OR
(myfield like '%bar: [0-9],[0-9],[3-9],[0-9];%')

If you have 10 as a number, you need to add more conditions where 10 is specified explicitly
0
 
drl1Author Commented:
Thanks, I got it working in that manner just before you posted. So any values above 9 have to be coded explicitly and a range can't simply adopt the syntax [0-20] or [0-100]....good to know!

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.