-1

I need to determine if all rows in varchar column in a db contain any characters outside of the particular set below: 

abcdefghijklmonpqrstuvwxyzABCDEFGHIJKLMONPQRSTUVWXYZ.-#,1234567890/\&%();:+@_*?|=''

I tried this but am not sure if it is correct:

select AccName 
from Transactions 
where AccName not like '%[!abcdefghijklmonpqrstuvwxyzABCDEFGHIJKLMONPQRSTUVWXYZ.-#,1234567890/\&%();:+@_*?|='']%'

Should this work?

Any help appeciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Maximojo
  • 315
  • 5
  • 17

2 Answers2

0

You cannot use a regular expression inside an ordinary LIKE condition in a query. If you want to use regular expressions, you will have to use a special operator. In MySQL, you could try the following:

SELECT AccName 
FROM Transactions 
WHERE AccName REGEXP [!abcdefghijklmonpqrstuvwxyzABCDEFGHIJKLMONPQRSTUVWXYZ.-#,1234567890/\&%();:+@_*?|='']%';

If this doesn't run to boot, then you may have to tidy up the regular expression you gave. And as marc_s asked, the exact regular expression and query will depend on the DB system you are using.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hi, I'm following what's here but the syntax is confusing to me: https://msdn.microsoft.com/en-us/library/ms179859.aspx – Maximojo Mar 04 '15 at 23:43
  • There is no built in support for regular expressions in TSQL (SQL Server). Are we correct in assuming that your database is MS SQL Server? Have a look at this article for how you can get regular expressions working in your case: http://stackoverflow.com/questions/3635150/how-do-i-select-a-regex-match-from-a-text-varchar-in-ms-sql – Tim Biegeleisen Mar 05 '15 at 00:55
  • Yes, sorry it is MS SQL server 10.5.55 (though I use several versions). Apologies I will be more specific in the future – Maximojo Mar 05 '15 at 01:35
-1

Database management systems vary in their support for matching regular expressions. Examples below use PostgreSQL, which supports POSIX regular expressions, along with other flavors. Examples below also test for case-sensitive matches to avoid sentences like "'Mike' doesn't not match the regular expression".

AFAIK, no DBMS lets you mix the like operator with a regular expression.

A like expression in the form column_name like '%a%' will match 'a' if it appears anywhere in the column. But you need your regular expression to match on the whole value of the column. Anchor the regular expression at the start and end of each value (^ and $), and tell the dbms to match one or more instances (+) of the atom. 

select 'Mike' ~ '^[a-zA-Z0-9]+$';  -- 'Mike' matches the regex

Write a failing test.

select 'Mike?' ~ '^[a-zA-Z0-9]+$';  -- 'Mike?' doesn't match the regex

Add the question mark to the regex, and verify the test succeeds.

select 'Mike?' ~ '^[a-zA-Z0-9?]+$'; -- 'Mike?' matches the regex

Repeat failing test and succeeding test for each character. When you've caught all the characters you want, invert the logic using the !~ operator in place of the ~ operator.

When your data is clean move this into a CHECK constraint.

PostgreSQL pattern matching

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185