anyone have a snazzy way to count substrings?

Hi All,

I have a varchar that contains a comma-delimited list of integers, such as "12,34,56,78,123,1,123455".

I need a way to count the number of numbers in the string (or, perhaps, better stated as "I need a count of substrings") ;)

I'm thinking there must be a number of ways to calculate the number of numbers in my list of numbers, but I can only seem to come up with looping through the string/varchar and counting the number of commas, and then adding one to that final count.

Anybody know of a "cooler" way to do this? Everything I can think of involves stepping through a character at a time...

any (printable) thoughts?
THanks!I think it was Brett who came up with this method:

select (len(YourString) - len(replace(YourString, ',', ''))) + 1|||Outstanding!!! I thought about using "replace" but didn't come up with near as nice an idea - I guess that's why you guys get paid the big bucks! :D

I know you were just free-handin' it, but the actual syntax is thus:
select (len(@.YourString) - len(replace(@.YourString,',', '')) + 1)

Exactly what I was looking for though...THANKS!!!

You guys never let me down...someday I hope to bring a little something to the party besides questions! :D|||I think it was Brett who came up with this method:

select (len(YourString) - len(replace(YourString, ',', ''))) + 1

Thanks...not hardly...I think it was Nigel who showed me...

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578

Although if you think about it's perfectly logical...

Once I saw that one, everytime I see a "complicated" problem, I step back and look for an easy answer...

Doesn't always work for me...

The theta join stuff still gives me pause...|||The theta join stuff still gives me pause...
piece o' cake

it's just a cross join with a loose condition

in fact, an inner join is just a cross join with a more restrictive condition

here's a perfect example of a theta join --

http://www.dbforums.com/showthread.php?p=3671683#post3671683|||The only way it works if you give something back. I use to be that way. Only asking question and no even trying to help someone else out there. Just remember that. Even if you take one question a week. I try to do at least one a day during the week.|||Gotcha GarryDawkins,

I am a member in quite a few forums related to the restoration of classic cars ;) so I know the importance of contributing. Otherwise, if everyone asks the questions without answering any, ummm...well...the forums would be quite a bit more boring *LOL*

I've got about 15 years of SQL experience, though mostly on Tandem Nonstop and Oracle, but have just recently started out with SQL Server, so am sure I can contribute once I've gotten past the initial curve.

Meanwhile, I'll ask questions and appreciate the knowledge and willingness to help of everyone else! and the help IS appreciated!

In fact, I did run across (on another forum, I think) a GREAT example of the reverse of this thread, which is how to build a comma-delimited string for use in, for example, a "dynamic" IN... clause...(the lead-in and example are modified to my application) - I posted this in an internal forum at my company).

I needed a way to do both, and found a way to do it easily with a function I didn't even know existed before yesterday! *L*:

Another, more efficient alternative is to use the COALESCE function,
which is much more efficient than the use of the cursor option, and also
shortens the cursor-supporting code block to a single select statement:

DECLARE @.PortfolioList varchar(100)

SELECT @.PortfolioList = COALESCE(@.PortfolioList + ',', '') +
CAST(PortfolioID AS varchar(5))
FROM Portfolio
ORDER BY PortfolioID

SELECT @.PortfolioList as CSVList

which results in the following output:
CSV_List
----------------------
11,67,90,100,105,110,115,120,125,130,135,140,145,1 50,155

(1 row(s) affected)

The COALESCE function performs the magic here. When @.PortfolioList is NULL
(the first row processed), it returns an empty string. On subsequent rows, it
concatenates the @.PortfolioList value with a comma and the current
PortfolioID value.|||Damn...that just looks sooooooooooo familiar...

Using COALESCE to Build Comma-Delimited String (http://www.sqlteam.com/item.asp?ItemID=2368)|||In fact, I did run across (on another forum, I think) a GREAT example of the reverse of this thread,

and quite interestingly so, eh? ;) In case anyone misinterpreted my comment and is thinking about wacking me with a plagarism stick, I also reworded my "example is mine" to read, correctly, that "the lead-in and example are modified to my application"

I have no interest in taking credit for the creativity of others, though I with great frequency use it with great glee and bastardize it without remorse or hindsight to be of use within my own evil empire. BwahahahahaHAHAHA!|||I have no interest in taking credit for the creativity of others, though I with great frequency use it with great glee and bastardize it without remorse or hindsight to be of use within my own evil empire. BwahahahahaHAHAHA!
good developers steal

great developers steal and pass it on to others

;)|||good developers steel

great developers steel and pass it on to others

;)

Or they can copper, iron, nickel or gold

:D|||Or they can copper, iron, nickel or gold

:D

I once tried to make some little spheres of tin and copper, but it got too hot and mixed together somehow, and all I ended up with was a HUGE mess and a pair of bronze balls. *sigh*

...but I digress...

hey, if you jack your own thread, is it still a thread-jacking? :D|||i've had my balls bronzed, too|||Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...|||Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...

Ouch, that's gonna leave a scar... :D|||Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...

wanna see the big version? http://rudy.ca/quatsch.cfm|||Ah. Definitely after... :o