-1

Just a version 2 of the above query…

Surname GivenName ABC x.yz ABc x.y.z ABC X.YZ A.BC xyz

The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters together(one after the other). In this case the output will be

abc x.yz a.BC xyz

How would I find just the names that have 2 non alphanumeric chars together(one after the other)

user3411162
  • 19
  • 2
  • 3
    "I know how to find if there are more than 1 non alphanumeric chars but not sure how to find if there are more than 1 non alphanumeric chars?" - You are contradicting yourself? – user1616625 Mar 12 '14 at 14:32
  • 1
    I meant I know how to find non alphanumeric chars but not sure how to find only those with more than 1 – user3411162 Mar 12 '14 at 14:43

1 Answers1

0

Assuming you want rows where either column has > 1 non-alphanumeriuc:

select
    Surname, 
    GivenName
from 
    T
where
    patindex('%[^A-Z0-9]%[^A-Z0-9]%', Surname) 
    + patindex('%[^A-Z0-9]%[^A-Z0-9]%', GivenName) > 0
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Just a version 2 of the above query… Surname GivenName ABC x.yz ABc x.y.z ABC X.*YZ A*.BC xyz The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters together(one after the other). In this case the output will be abc x.*yz a*.BC xyz How would I find just the names that have 2 non alphanumeric chars together(one after the other) – user3411162 Mar 12 '14 at 17:57
  • Use the pattern `'%[^A-Z0-9][^A-Z0-9]%'` – Alex K. Mar 12 '14 at 17:58
  • Thanks. I want to ignore the spaces. How would I exclude that? – user3411162 Mar 12 '14 at 18:08
  • Remove them before the pattern test; `... where patindex('%[^A-Z0-9]%[^A-Z0-9]%', replace(Surname, ' ', '')) + ...` – Alex K. Mar 12 '14 at 18:09