Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

181 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
2875 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

181 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  
 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.07 seconds. Powered By: Snitz Forums 2000