Lynn Pettis (4/19/2013)
I was doing some testing, code below:<snipped>
Results:
Beginning execution loop
Batch execution completed 10000 times.
----- Using DelimitedSplit/CROSS APPLY -----
CPU(ms): 422 Logical Reads: 80564 Elapsed(ms): 1667 Reads: 80 Writes: 0
----- Using PATINDEX -----
CPU(ms): 109 Logical Reads: 245 Elapsed(ms): 110 Reads: 0 Writes: 0
I see the cleverness of using PATINDEX to check for the existence of a substring of four consecutive non-comma characters and agree that it works for the minimal specifications that the OP provided.
If, however, the values in these comma-delimited strings are supposed to be numbers (and not strings of numerals) and what the OP really wants is any row where the comma-delimited string includes a value >= 1000 (on which I wish the OP had provided some clarification or that I had asked for such), the PATINDEX solution won't work. It will return 1 for the string '100, 192, 38, 3' because the second value consists of [space][1][9][2] - four non-comma characters - but 192 is not >= 1000. The string-split method using LEN() as both Alan.B and I wrote it will do the same thing, but it can be easily modified to convert the parsed values to numeric datatypes and compare them to the reference value as numbers to get only rows where at least one parsed, converted value >= 1000.
Jason Wolfkill