Help Needed - 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 2008 Forums

Transact-SQL (2008)

Help Needed

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-11-30 : 12:26:47

I have a table which holds a reference number, however past oprartor have not made the references up correctly. Part of the reference is an internal id number for a client. What I am trying to do is get the following

Sample data
132116234CL2178170
1343198390CL217817
166841312CL2178170
180485687CL2178170
1813994454CL217817
217020
364981625CL2178170
39315843CL21781700
613500875CL2178170
CC100747CL217748
CC101273CL217748
CC102782CL217748
CC103652CL217748

If there is a CL in the reference the id is the next 6 digits. If no CL then the id is the first 6 digits
what I want to get out is just the distinct licence numbers
so I should end up with
217817
217020
217748

code so far that returns all records
SELECT
CASE
WHEN CHARINDEX('CL', reference, 1) > 0 THEN SUBSTRING(reference , CHARINDEX('CL', reference, 1)+ 2, 6)
ELSE LEFT(reference,6 )
END as 'ClientID'
FROM #ADDACSBODY

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-30 : 12:47:05

I think you just nee a DISTINCT

SELECT DISTINCT
CASE
WHEN CHARINDEX('CL', reference, 1) > 0 THEN SUBSTRING(reference , CHARINDEX('CL', reference, 1)+ 2, 6)
ELSE LEFT(reference,6 )
END as 'ClientID'
FROM #ADDACSBODY

Jim

Everyday I learn something that somebody else already knew

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-11-30 : 12:48:23

Cheers Jim,

Works a treat

Pete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources