March 15, 2018 at 2:20 pm
Hi Guys,
I have one task where i need to pull Name with some selected Special Characters, like names have letters from a to z or a space or a hyphen or a photostatic . and exclude all other names containing other special character
see below sample data
CREATE TABLE #TEMP
(NAME VARCHAR(100))
INSERT INTO #TEMP VALUES ('SOHN&SHER')
INSERT INTO #TEMP VALUES ('SOHN-SHER')
INSERT INTO #TEMP VALUES ('SOHN SHER')
INSERT INTO #TEMP VALUES ('SOHN(S')
INSERT INTO #TEMP VALUES ('SOHN{S')
INSERT INTO #TEMP VALUES ('O''Brien')
I just need to pull below names
SOHN-SHER
SOHN SHER
O'Brien
do anyone know how to pull this?
Thanks for your help.
March 15, 2018 at 4:38 pm
select *
from #TEMP t
where patindex('%[^A-Z ''-]%', name) = 0
will do the trick here - but be aware that depending on your collation you may need more coding such as lowercase and/or convert special characters.
March 16, 2018 at 7:09 am
frederico_fonseca - Thursday, March 15, 2018 4:38 PMselect *
from #TEMP t
where patindex('%[^A-Z ''-]%', name) = 0will do the trick here - but be aware that depending on your collation you may need more coding such as lowercase and/or convert special characters.
Thanks for your reply.
It works, but i have one more case where 2 dotes are coming in one string and i don't want to exclude, can you please help me how i do that,
The Name is : 'rone, Sr.'
March 16, 2018 at 8:04 am
any further char that you wish to include add them to the list
select *
from #TEMP t
where patindex('%[^A-Z ''-]%', name) = 0
[^A-Z ''-] means any character except those after the ^ and before the closing ]
so if a name with a "." should be included change to
[^A-Z. ''-]%
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply