Avatar of Andy Green
Andy Green
Flag for United Kingdom of Great Britain and Northern Ireland asked on

SQL Count depending on the way the data is presented

Hi Experts

I have rows of (string) id data for example


123456
789012
*1234632*
*7645281*
*45reartf*

What I'm trying to do is separate the counts for those with * at each end and those without. So for the above I need 2 and 3

Is there a simple way to achieve this please

Andy
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ste5an

E.g.

SELECT SUM(IIF(Q.StringData LIKE '%*%', 1, 0)) AS WithAsterisk ,
       SUM(IIF(Q.StringData LIKE '%*%', 0, 1)) AS WithoutAsterisk
FROM   (   VALUES ( '789012' ) ,
                  ( '*45reartf*' ) ,
                  ( '123456' ) ,
                  ( '*1234632*' ) ,
                  ( '*7645281*' )) Q ( StringData );

Open in new window

SQL Server 2012+
Andy Green

ASKER
Thanks - I'm using sql Server 2008. Is this for a later version, to run or psedo code to give an idea?

Andy
ASKER CERTIFIED SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

SELECT
    SUM(CASE WHEN LEFT(id_string, 1) = '*' AND RIGHT(id_string, 1) = '*' THEN 0 ELSE 1 END) AS count_without_star,
    SUM(CASE WHEN LEFT(id_string, 1) = '*' AND RIGHT(id_string, 1) = '*' THEN 1 ELSE 0 END) AS count_with_star
FROM dbo.table_name
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
awking00

>>What I'm trying to do is separate the counts for those with * at each end and those without. So for the above I need 2 and 3<<
If what you want is the count of records starting and ending with an asterisk, then
select sum(case when id like '*%*' then 1 else 0 end) as cnt
from yourtable
By selecting only records 2 and 3, If what you want is the count of records starting and ending with an asterisk with only numbers in between, then
select sum(case when id like '*%*' and id not like '%[A-Za-z]' then 1 else 0 end) as cnt
from yourtable
ste5an

btw, my pattern counts as the column alias name says rows with or without asterisk. If you want to count exactly for start and end asterisks, then the pattern is '*%*':

SELECT SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 1
                ELSE 0
           END) AS WithAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 0
                ELSE 1
           END) AS WithoutAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 1
                ELSE 0
           END) AS WithStartAndEndAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 0
                ELSE 1
           END) AS WithoutStartAndEndAsterisk
FROM   (   VALUES ( '789012' ) ,
                  ( '*45reartf*' ) ,
                  ( '123456' ) ,
                  ( '*1234632*' ) ,
                  ( '*7645281*' ) ,
                  ( 'Don''t*count*me' ) ,
                  ( '*Neither*me..' ) ,
                  ( '..nor*me*' )) Q ( StringData );

Open in new window

This was essentially a typo in my first post and I didn't notice it prior now.
awking00

I don't know if there is any difference in performance but the second option could be written as
select sum(case when id like '*%*' and patindex('%[^0-9]%',id) >0 then 1 else 0 end) as cnt
 from yourtable
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ste5an

@awking: What pattern should this catch or exclude?

SELECT SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 1
                ELSE 0
           END) AS WithAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 0
                ELSE 1
           END) AS WithoutAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 1
                ELSE 0
           END) AS WithStartAndEndAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 0
                ELSE 1
           END) AS WithoutStartAndEndAsterisk ,
       SUM(CASE WHEN StringData LIKE '*%*'
                     AND PATINDEX('%[^0-9]%', StringData) > 0 THEN 1
                ELSE 0
           END) AS AwkingCount
FROM   (   VALUES ( '789012' ) ,
                  ( '*45reartf*' ) ,
                  ( '*45re*artf*' ) ,
                  ( '123456' ) ,
                  ( '*1234632*' ) ,
                  ( '*7645281*' ) ,
                  ( 'Don''t*count*me' ) ,
                  ( '*Neither*me..' ) ,
                  ( '..nor*me*' )) Q ( StringData );

Open in new window

Capture.PNG
awking00

It catches precisely what you would expect. The AwkingCount of 4 was only to catch the count of records starting and ending with an asterisk. What I was trying to say regarding your last post was that only three counts were necessary, asterisks at start and end, no asterisks, and asterisks not at start and end. While it's not real hard to figure out that the WithStartAndStreakAsterisk count is made up of 2 without and 3 with asterisk but not at start and end, it would be more intuitive to show 3 with asterisk but not at start and end, 2 without asterisk, and 4 with asterisk at start and end for a total of 9 which equals the overall total of records. To get the count of records with asterisk but not at start and end, you could use sum(case when charindex('*',Q.StringData) > 1 then 1 else 0 end) as WithAsteriskNotAtStartAndEnd and eliminate the WithoutAtStartAndEndAsterisk calculation.
ste5an

ahh, my sample last had four values, cause I initially simply mistyped the pattern. I've included them to show the difference. The first two with the wrong pattern for the problem, cause they simple count any asterisk and the second which go for starting/end asterisks only.
Thus just take my first post and change the pattern.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott Pletcher

This is all irrelevant to the original q, which was just to count rows that had '*' at each end and those that didn't, period.  
ste5an

Scott, as I wrote, my first pattern was incorrect. And I've given the four values as demonstration of the difference. Thus it is relevant.
Scott Pletcher

awking did not ask the q.  He appears to have misread the initial q and thus gone off on a tangent.  I fail to see the relevance to the actual q.  Please explain how it's relevant to that.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.