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 );
This was essentially a typo in my first post and I didn't notice it prior now.
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
SQL Server 2012+