T SQL search instring
Posted on 2013-01-19
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)...
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...
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.