Kenneth Fisher (1/7/2008)
Actually there is a flaw in your logic. I agree if I am compairing '1' to '2,3,12' I'm going to get a false hit. However I'm compairing ',1,' to ',2,3,12,'. Thats why you add the extra comma at the beginning and the end. Then you add a comma at the beginning and end of your value. That eliminates your false hits.Also while using a number table may help in this case the other method works on any delimited list. Not just a list of numbers.
For example ',John,Joe,Jim,' LIKE '%,'+First_Name+',%'
I will certainly be running some tests using the number table. I'll be interested to see how it compares out when you have 100 or more items in the list and are returning back several thousand rows.
My StringParser function will accept any string with any single-character delimiter. It works on numbers, names, street addresses, whatever. I've run it with up to 3,000 values and it works quite well. (For long lists, it needs more numbers. My production servers have a Numbers table in a Common database with values from 0 to 100-million.)
Adding commas at the beginning and end of the like statement will eliminate false results. But it doesn't help with the cost and the speed. That's why I mentioned that the "Like" solution requires an index scan, while my join solution uses an index seek. Another post in this thread had the same results I did.
The "Like" solution is only better if your string parsing UDF is built poorly, using a cursor or a while loop. If it uses a simple numbers table, the "Like" solution is slower and more expensive.
Again, take a look at the data from Robyn Page and Phil Factor that I referenced in my first post. It will make it much more clear. (They're better writers and have more data than I.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon