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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:341271
Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
