SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help Needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

156 Posts

Posted - 11/30/2012 :  12:26:47  Show Profile  Reply with Quote
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
2869 Posts

Posted - 11/30/2012 :  12:47:05  Show Profile  Reply with Quote
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
Go to Top of Page

Pete_N
Posting Yak Master

156 Posts

Posted - 11/30/2012 :  12:48:23  Show Profile  Reply with Quote
Cheers Jim,

Works a treat

Pete
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000