SQLTeam.com | Weblogs | Forums

How to get data from signature key where not like stars?

I work on SQL server 2012 I faces issue i need to select data that not have stars only meaning i need only to modify my condition
when make select

i need to modify PATINDEX('%[^a-zA-Z0-9]%', f.Data) <> 0 to accept stars only
if stars only then return it
if stars with characters then not return it

Expected Result
f.Data must be one star or multiple star
f.Data must not have any charterer letter from A to z small or capital

select r.*, f.* 

from #Ref r
cross apply
dbo.Split(r.SignatureKey,'$') f where r.GroupId = f.Id 
and
PATINDEX('%[^a-zA-Z0-9]%', f.Data) <> 0 

Create table #Ref
(
SignatureKey  nvarchar(50),
GroupId int,
PortionKey nvarchar(50),
Status nvarchar(100)
)
insert into #Ref(SignatureKey,GroupId,PortionKey,status)
values
('*$*$C$***$**$**$*',3,'s',NUll),
('*$*$*$FG344$*$**$*',4,'F',NUll),
('*$*$5$***$*$D$*',6,'D',NUll),
('*$****$*$***$***$**$*',2,'g',NUll),
('*$**$*$***$L$**$*',5,'f',NUll)

Expected result

SignatureKey GroupId PortionKey Status Id Data
*********** 2 g NULL 2 g

my issue that face here if stars and letter display
but i need to display f.data that have stars only without letters from a to z

hi .

i am going to start charging money !! :wink: just joking !

this is NOT case Sensitive ( i mean collation ) .. anything upper and lower case letters QUALIFY

select 
   * 
from 
   ref 
where 
   SignatureKey not like '%[a-z]%'

image
image