Character count to determine units

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Character count to determine units

Author

Topic

gdarby
Starting Member

2 Posts

Posted - 2004-09-23 : 16:29:28

I have a situation where I have a column in a table that containes multiple 3-digit comma separated numbers that represent products.

Example: 133,256,324,166,634

I need to get a simple count of the products like 133,256,324,166,634 = 5

If I counted the characters and then did a series of ranges to determine the amount, I would appreciate your ideas.

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-23 : 16:32:40

Are the productid's always 3 digit? Why not use a many to many relationship.

if they are always 3 digit you could...

LENGTH(REPLACE( productids, ',', '' )) / 3

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 16:38:45

Character count would be..

Select len(productIds)-len(replace(productIds,',',''))

So the product count should be

Select len(productIds)-len(replace(productIds,',',''))+1

Corey

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-23 : 16:46:29

slick

gdarby
Starting Member

2 Posts

Posted - 2004-09-23 : 22:19:53

Awesome, Seventhnight...

Worked perfectly.

Thanks for the fast response!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources