Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SMerrill
Posting Yak Master
206 Posts |
 Posted - 2006-11-18 : 16:35:44
|
| I need to identify all name fields with "bad characters" so I decided to use LIKE '%[^A-Z ''a-z/-]%'.Please note that this syntax says: "where any character in the field is not one of these permitted characters." However, this matches names with dashes like "MENDEZ-SMITH", which I'm trying to permit.I have also tried LIKE '%[^A-Z ''a-z[-]]%' but this made it worse because it began to tolerate dollar-signs, which are always illegal in a name field.What I need to pass through:A-Z, a-z, hyphen, apostrophe, ampersand, period, space.Please provide the appropriate LIKE clause for passing those characters. If I need to use REGEX instead of LIKE, just let me know. I know how to do that.~ Shaun MerrillSeattle, WA |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-18 : 21:01:41
|
this seems to work:declare @t table (name varchar(100))insert into @t select 'George Washington-Menendez''s Burger & Fry Hut...' union allselect 'this should be excluded because it has exluded chars: 333'select * from @t where name not like '%[^-''&. a-zA-Z]%'
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
 |
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2006-11-21 : 02:05:31
|
| THANKS, that works. I hope the LIKE statement has been exhaustively tested!~ Shaun MerrillSeattle, WA |
|
|
|
|
|
|
|
|
Bad Characters Search