Very Puzzling T-SQL question

Question

Post reply

Very Puzzling T-SQL question

mdonald 98074

SSC Enthusiast

Points: 155
More actions
January 10, 2012 at 1:58 pm

#251259

Hi guys, I have a dilemma. I need to count something that is not easily countable. Allow me to show you:
icd9_code
________
251.03|V102.0|341.06^03.4
I need to count the codes in that string (it's in a column called icd9_code). The total would be 4 codes. But I don't want to count the pipes, that is just a separator between codes, nor do I want to count the carats, that just separates the procedures. Other than doing some function that pareses through it, is there a way that I don't know of that will do this?
Thank you guys in advance for looking at this.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

The total would be 4 codes......

and they are...???

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

mdonald 98074 SSC Enthusiast Points: 155 More actions · Answer 2

The items counted would be 251.03 , V102.0, 341.06, and 03.4

Jared SSC Guru Points: 61793 More actions · Answer 3

If you are asking if there is a function that can magically count arbitrary items in a non defined list, it does not exist 🙂 You should count your delimiters and add 1 to it or parse it out using Jeff Moden's updated splitter and then count the results.

Jared
CE - Microsoft

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

So why don't you "want" to count the seperators? That would be the most direct way to count the codes.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

mdonald 98074 SSC Enthusiast Points: 155 More actions · Answer 5

You guys are right. I will go the route of counting my delimiters and adding 1. Thank you. Sometimes you look at something for so long, you forget the easy things.

🙂

Jared SSC Guru Points: 61793 More actions · Answer 6

mdonald 98074 (1/10/2012)
You guys are right. I will go the route of counting my delimiters and adding 1. Thank you. Sometimes you look at something for so long, you forget the easy things.
🙂

Don't forget to use a tally table! 😎

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQLKnowItAll (1/10/2012)
mdonald 98074 (1/10/2012)
You guys are right. I will go the route of counting my delimiters and adding 1. Thank you. Sometimes you look at something for so long, you forget the easy things.
🙂
Don't forget to use a tally table! 😎

I prefer LEN if I have to count the occurrences of something in a string.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 8

GilaMonster (1/10/2012)
SQLKnowItAll (1/10/2012)
mdonald 98074 (1/10/2012)
You guys are right. I will go the route of counting my delimiters and adding 1. Thank you. Sometimes you look at something for so long, you forget the easy things.
🙂
Don't forget to use a tally table! 😎
I prefer LEN if I have to count the occurrences of something in a string.

With or without REPLACE()? 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

LutzM (1/10/2012)
GilaMonster (1/10/2012)
SQLKnowItAll (1/10/2012)
mdonald 98074 (1/10/2012)
You guys are right. I will go the route of counting my delimiters and adding 1. Thank you. Sometimes you look at something for so long, you forget the easy things.
🙂
Don't forget to use a tally table! 😎
I prefer LEN if I have to count the occurrences of something in a string.
With or without REPLACE()? 🙂

Both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass