0

I need to select the rows that contain non alphanumeric values in names. I got part of the solution, but, I am also getting rows with spaces, hyphens and single quotes. So, I need to pull the rows with that are alphanumeric and also not pull rows with space and - and '.

Did some research online

select * 
from EMP  
where NAME like '%[^a-z,1-9]%'

I don't want to get rows with - or spaces or '

Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
user11724267
  • 1
  • 1
    In (standard) SQL, the only wildcards that `LIKE` supports is `%` for "any number of characters" and `_` for "a single character". You seem to assume that LIKE supports some kind of regular expression syntax in SQL - this is not the case. At least not in standard SQL (which the tag `sql` refers to) –  Jul 01 '19 at 13:28
  • @a_horse_with_no_name pretty sure the topicstarter meant SQL Server (MSSQL) here. As SQL Server supports some very limited regex support in the [LIKE](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017) operator. – Raymond Nijland Jul 01 '19 at 13:31
  • @RaymondNijland: we don't know that. Maybe the OP simply found some answers that claimed that "SQL" would support that and now she/he is surprised that this doesn't work with a DBMS respecting the SQL standard. –  Jul 01 '19 at 13:33
  • "we don't know that" we "know" or atleast pretty sure @a_horse_with_no_name notice what the topicstarter said "but, I am also getting rows with spaces, hyphens and single quotes" so he is using a DBMS which respects this SQL dialect .. or he is using sybase which is also possible. – Raymond Nijland Jul 01 '19 at 13:43
  • @a_horse_with_no_name [FYI](https://stackoverflow.com/questions/1919757/sql-server-2008-query-to-find-rows-containing-non-alphanumeric-characters-in-a-c) – MJoy Jul 01 '19 at 13:44
  • 1
    @MJoy i do agree with a_horse_with_no_name by the way, the topicstarter should have provided way more information and or used the correct tagging.. Not to mention providing example data and expected results. – Raymond Nijland Jul 01 '19 at 13:52

4 Answers4

1

Here is a way using number table to find bad chars:

create table #GoodLetters(letter char(1))

insert into #GoodLetters
values
 ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')
,('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
,('-'),(' '),('''')

--select * from #GoodLetters

select a._key, a.name,substring(a.name, v.number+1, 1) 
from (select 'Your Key' _key,name from emp ) a
    join master..spt_values v on v.number < len(a.name)
    left join #GoodLetters GL on substring(a.name, v.number+1, 1)=GL.letter
where v.type = 'P'
    and GL.letter is null

drop table #GoodLetters
KeithL
  • 5,348
  • 3
  • 19
  • 25
0

I think it should go like that:

select * 
from EMP  
where (NAME NOT LIKE '%-%' OR NOT LIKE '%[ ]%' OR NOT LIKE '%\'%' ESCAPE '\');

  • '%[ ]%' --> gives you one or more spaces

  • ESCAPE --> requiered for special character

Luke
  • 23
  • 7
0

you can also add ' , - and space chars to the regex.

select * 
from EMP  
where NAME like '%[^a-z,1-9''- ]%'
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44
0

Hyphen is a little tricky. Because it is used for character classes, it needs to be the first character in the class. So:

where name like '%[^- a-z,1-9]%'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786