SQL had a slimmed down version of Reg Ex that you could use with the......CHARINDEX function i think....i may have to revisit that to see if it will work, but if memory serves it was too limited for what i wanted.
On Fri, Aug 5, 2011 at 8:46 AM, Cameron Childress <[email protected]>wrote: > > 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:341280 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
