Help with counting values that have different endi

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

General SQL Server Forums

New to SQL Server Programming

Help with counting values that have different endi

Author

Topic

larryg003
Starting Member

18 Posts

Posted - 2010-06-11 : 15:18:19

Hi, sorry I had to repost this question, but I am looking for a different way to do it.

I have A column with a bunch of ID tags
[Column]
ASD.L
HSJ.L
SDS.ZY
DMS.S
SDJ.A
ASD.A
ASWERT.G
...
etc..

I am looking for a way to select/count the number of distinct prefixes of the ID tags.
As you can see above ASD is listed twice, but I am only concerned with it being counted as distinct 1.
As such my list up to this point would be
ASD, HSJ, SDS, DMS, SDJ, ASWERT. I don't know all of the prefixes of the ID tags, so I can't specify them. I do however have all of the endings: .L, .ZY, .S, .A, .G.

Is there a way to make SQL select only the distinct values by ignoring the .L, .ZY, .S, .A and .G endings?

I will be eternally grateful if someone managed to help me with this issue. I am using SQL management studio.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-11 : 15:31:28

select count ( distinct parsename([column],2))
from yourTable
group by parsename([column],2))

Jim

Everyday I learn something that somebody else already knew

larryg003
Starting Member

18 Posts

Posted - 2010-06-11 : 15:45:02

Thanks Jim,

but it said the following:
MSG 8116, Level 16, State 1, Line 1
Argument data type ntest is invalid for Argument 1 of parsename function.

8..(

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-11 : 16:26:03

convert to varchar(max)

select count ( distinct parsename(convert(varchar(max),[column]),2))
from TableA
group by parsename(convert(varchar(max),[column]),2)

larryg003
Starting Member

18 Posts

Posted - 2010-06-11 : 16:58:52

OMG IT WORKS!!!!

It gave me 376 values. That means that there are 376 distinct values listed right?

As a sidenote, is there a way to make it so I can see what all of those Tags were?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 09:57:34

Remove the COUNT and just select the values.

select distinct parsename(convert(varchar(max),[column]),2)
from TableA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources