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 2005 Forums
 Transact-SQL (2005)
 SELECT STMT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Brittney10
Posting Yak Master

USA
152 Posts

Posted - 09/19/2013 :  12:25:20  Show Profile  Reply with Quote
I'm trying to query a table that contains two different formats in a CODE field. One format of the code is like 'XXX*XXXX*XXX' and the other format is like 'XXX*XXXX'. I need to be able to get a count of each format (i.e. there are 100 in 'XXX*XXXX*XXX' format and 200 in 'XXX*XXXX' format). Any help would be greatly appreciated.

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 09/19/2013 :  12:44:38  Show Profile  Reply with Quote
What is the rule to distinguish the two types? Can the lengths give an indication? Does the * represent a single character , or any number of characters? If one is of length 12 and the other is of lenght 8, then you could do this:
SELECT
   SUM(case when len(code) = 12 then 1 else 0 end) as Count12,
   SUM(case when len(code) =  8 then 1 else 0 end) as Count8
FROM
   YourTable;
Go to Top of Page

Brittney10
Posting Yak Master

USA
152 Posts

Posted - 09/19/2013 :  13:52:21  Show Profile  Reply with Quote
The lengths of the codes will vary, so I can't rely on length alone. I could have XX*XXX*XXX and XXXX*XXXXX (length is the same).

Edited by - Brittney10 on 09/19/2013 13:52:46
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 09/19/2013 :  14:32:26  Show Profile  Reply with Quote
Can you post some actual data? What is in "*"? The way I understood your description, a string such as ABCDEFGHIJ could match either pattern XX*XXX*XXX or XXXX*XXXXX. If that be the case, how do you determine which one it belongs to?

Is * a wildcard indicator, or is it the specific character '*', or is it something lese?
Go to Top of Page

Brittney10
Posting Yak Master

USA
152 Posts

Posted - 09/19/2013 :  14:48:12  Show Profile  Reply with Quote
The "*" is actually a special character in the Code itself. It's used to segment the code field. Some actual examples may be "US*12345*Texas", "12345*Texas". This is a cleanup effort, so the data is poorly stored/formatted, why I'm trying to pull out the separate formats, so we can clean them up. The "*" characters are my delimiters so i can distinguish between the two formats. Thanks for your help btw!

Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/19/2013 :  14:53:28  Show Profile  Reply with Quote
select sum(case when col like '%*%' then 1 else 0 end) as [XXX*XXXX format],
sum(case when col like '%*%*%' then 1 else 0 end) as [XXX*XXXX*XXX format]
from table_name
Go to Top of Page

Brittney10
Posting Yak Master

USA
152 Posts

Posted - 09/19/2013 :  14:56:54  Show Profile  Reply with Quote
Unfortunately the LIKE clause in the first statement, captures the all the values that are captured in the second LIKE statement, so this will not work.

quote:
Originally posted by sigmas

select sum(case when col like '%*%' then 1 else 0 end) as [XXX*XXXX format],
sum(case when col like '%*%*%' then 1 else 0 end) as [XXX*XXXX*XXX format]
from table_name



Edited by - Brittney10 on 09/19/2013 14:58:57
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/19/2013 :  15:02:49  Show Profile  Reply with Quote
select sum(case when col like '%*%' and col not like '%*%*% then 1 else 0 end) as [XXX*XXXX format],
sum(case when col like '%*%*%' then 1 else 0 end) as [XXX*XXXX*XXX format]
from table_name
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/19/2013 :  15:16:46  Show Profile  Reply with Quote
Or

select sum(case when len(col) = len(replace(col,'*,'')) + 1 then 1 else 0 end),
sum(case when len(col) = len(replace(col,'*,'')) + 2 then 1 else 0 end)
from table_name
Go to Top of Page

Brittney10
Posting Yak Master

USA
152 Posts

Posted - 09/19/2013 :  18:00:37  Show Profile  Reply with Quote
Your last post got me going in the right direction and i'm now returning the correct result sets. I was not aware you could use LIKE clauses such as you did (i.e. %*%*%), so your reply was most helpful.Thanks for your help!

quote:
Originally posted by sigmas

Or

select sum(case when len(col) = len(replace(col,'*,'')) + 1 then 1 else 0 end),
sum(case when len(col) = len(replace(col,'*,'')) + 2 then 1 else 0 end)
from table_name

Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/19/2013 :  18:27:57  Show Profile  Reply with Quote
Glad to help.
This post 09/19/2013 : 15:02:49 also return correct result set. try it.
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.09 seconds. Powered By: Snitz Forums 2000