SQL Count depending on the way the data is presented

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 );

Select allOpen in new window

SQL Server 2012+

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

Andy

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

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

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 );

Select allOpen in new window

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

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

@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 );

Select allOpen in new window

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.

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.

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.  

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.

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.