| 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 Count8FROM YourTable; |
 |
|
|
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). |
|
|
|
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? |
|
|
|
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! |
|
|
|
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 |
|
|
|
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
|
|
|
|
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 |
|
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-09-19 : 15:16:46
|
| Orselect 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 |
|
|
|
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
Orselect 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
|
|
|
|
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. |
|
|
|
|
SELECT STMT