I'm assuming refactoring table structure isn't an option or you'd have said so....
I suspect RegEx is going to be your answer, though a quick google for SQL Server UDF library didn't reveal anything as useful as CFLib for something like this. I suspect it's out there somewhere though... -Cameron On Fri, Aug 5, 2011 at 9:09 AM, GMoney <[email protected]> wrote: > I have a varchar column (SQL server 2008) that contains comma delimited > data > thusly: A,V, C, P, PP, R, RT, L, Z,Z,Z.... > > The members of this list will always be only 1 or 2 characters long. The 1 > or 2 character members can repeat any number of times and come in any > order. > I have a finite list of two character list members (for ease of > understanding, lets say my member set consists of all single characters > A-Z, > and all double character sets AA-ZZ). > > What i want to do sounds achingly simple: Count the number of occurrences > of > a 1 or 2 character list member in that list. > > So....if the list is: A,A,C,Z,PP,P,ZZ > > - How many times does "PP" occur? Return "1". > - How many times does "A" occur? Return "2". > - How many times does "P" occur? Return "1". > > I have a hack that i've implemented that basically takes the length of the > string MINUS the length of the string after i've removed the character I'm > looking for, similar too: > > select (((LEN(List) - LEN(REPLACE(List, 'A,', ''))) / LEN('A,')) + case > when > right(List, LEN('A,')) = ',A' then 1 else 0 end + case when List= 'A' then > 1 > else 0 end ) > > This works in all cases except one: When the list also contains the value > "AA", and that value is in the middle of the list. In that case, it counts > an extra "A" because it sees another "A," and counts that, but it shouldn't > because the value is actually "AA". > > Maybe i need to scrap my hack idea and try something using regular > expressions...? I think SQL allows you to use a slimmed down version of reg > ex, but I"m not sure. Anyway.....this is probably too much thinking for a > Friday, but if anyone's bored, i'd love to hear any ideas. > -- Cameron Childress -- p: 678.637.5072 im: cameroncf facebook <http://www.facebook.com/cameroncf> | twitter<http://twitter.com/cameronc> | google+ <https://profiles.google.com/u/0/117829379451708140985> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:341279 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
