DISTINCT Substring? - SQL Server Forums

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

Transact-SQL (2000)

DISTINCT Substring?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-07-26 : 04:41:04

Is it possible to return distinct values based on a string function?

I have the following

Parent1.Item1
Parent1.Item2
Parent2.Item1
Parent3.Item1

I nned to return the ditinct values before the first period, something like:-

SELECT DISTINCT Left(String,instr(string,".")) FROM ConfigStrings

Any ideas?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-26 : 04:52:01

Yeah you can

Somthing like this


Select Distinct LEft(String,6) From 
(
Select 'Parent1.Item1' String Union All 
Select 'Parent1.Item2' Union All 
Select 'Parent2.Item1' Union All 
Select 'Parent3.Item1'
)as  f

Chirag

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-26 : 05:20:18

or this (to be more specific)....

Select Distinct substring(string,1,charindex('.',string)-1) From 
(
Select 'Parent1.Item1' String Union All 
Select 'Parent1.Item2' Union All 
Select 'Parent2.Item1' Union All 
Select 'Parent3.Item1'
)as  f

Harsh Athalye
India.
"Nothing is Impossible"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-26 : 05:59:38

or this (to be more obtuse)....

Select Distinct parsename(string, 2) From 
(
Select 'Parent1.Item1' String Union All 
Select 'Parent1.Item2' Union All 
Select 'Parent2.Item1' Union All 
Select 'Parent3.Item1'
)as  f

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources