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 2005 Forums
 Transact-SQL (2005)
 SELECT STMT

Author  Topic 

Brittney10
Posting Yak Master

154 Posts

Posted - 2013-09-19 : 12:25:20
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 12:44:38
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

154 Posts

Posted - 2013-09-19 : 13:52:21
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).
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 14:32:26
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

154 Posts

Posted - 2013-09-19 : 14:48:12
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

172 Posts

Posted - 2013-09-19 : 14:53:28
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

154 Posts

Posted - 2013-09-19 : 14:56:54
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


Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 15:02:49
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

172 Posts

Posted - 2013-09-19 : 15:16:46
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

154 Posts

Posted - 2013-09-19 : 18:00:37
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

172 Posts

Posted - 2013-09-19 : 18:27:57
Glad to help.
This post 09/19/2013 : 15:02:49 also return correct result set. try it.
Go to Top of Page
   

- Advertisement -