Help with RegEx to find non alphanumeric characters

[bmacbmac]

Hi.

I have a table full of names. I am trying to find all names that contain a non alphanumeric character, but I want to exclude spaces, dashes - , and ampersand &.

I am trying this:

select * from mytable
where name like '%[^a-zA-Z0-9 -&/]%'

However, I am getting names returned like:
JELD-WEN
GIANT STOP-N-GO OF NEW MEXICO

I would think these names would not be returned because they are no NON alpha numeric characters except for the -.

What am I doing wrong in my script?

Thanks!

 

[ggriffit]

looks like you need to escape the special chars, see :

create table x (col1 varchar(255))
insert into x (col1) values ('test')
insert into x (col1) values ('test-with-dash')
insert into x (col1) values ('test-with£')
insert into x (col1) values ('test_with£')
insert into x (col1) values ('test_with&')
insert into x (col1) values ('test£')
select * from x where col1 like '%[^a-zA-Z0-9!&!-]%' escape '!'

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/