| Author |
 Topic  |
|
|
Pete_N
Posting Yak Master
117 Posts |
 Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/30/2012 : 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
117 Posts |
Posted - 11/30/2012 : 12:48:23
|
Cheers Jim,
Works a treat
Pete |
|
|
| |
Topic |
|
|
|
Help Needed
