Ok, so I am making a uploader to send data to the DB directly, but the front end input page has javascript that uses a JavaScript regex to make sure only these characters are accepted in the string: 0-9(),.;?:*%$&#@!`a-zA-Z\/s-_ I was having an issue with users uploading "s and while I can simply look for these I'd prefer to be exact and confirm that only the above characters are allowed. For now I'm trying to catch so I tried requesting records that were 'NOT LIKE '%[^0-9(),.;?:*%$&#@!`a-zA-Z/-_]%' I also add space, tab, vertical tab, cr and lf (encoded through VB). But I'm getting issues and my tests in Query Analyzer are very strange indeed, if I run the following: select t.comments from tempdb..[_UVIO127_0_0_134200892240AM] t where len(t.comments)<=250 AND t.comments not like '%[^0-9a-zA-Z,.;?:*&#@ -_]%' Returns: "Aberdeen" "Examiner", 5K circ $45/ prod 1345 circ """3456 cost 4567.89/ (which doesn't make sense since there are "s) But through experimentation, I found out that switching the - and ' ' (the 2nd and 3rd from the right in the regex) and execute: select t.comments from tempdb..[_UVIO127_0_0_134200892240AM] t where len(t.comments)<=250 AND t.comments not like '%[^0-9a-zA-Z,.;?:*&#@- _]%' That I return no results. I thought this was an issue with and /, not sure if they should be escaped or not, they seemed to work best without escapes, but I removed them completely and still I'm getting odd behaviour. I'm wondering if the '_' is being processed as any character somehow? I should never have to escape anything inside of []s, right? Any help would be greatly appreciated, this is SQL 2005 with the latest SP and I'm at a loss.
You are aware that the ^ at the start of a pattern (the bracketed list in a LIKE clause) is a wildcard in itself? LIKE '%[^xyz]%' ... is actually the same as ... NOT LIKE '%x%' AND NOT LIKE '%y%' AND NOT LIKE '%z%' So your NOT LIKE '%[^0-9]%' is the same as LIKE '%[0-9]%' To include a wildcard as a literal character, you have to put brackets around it (also inside a pattern): [_] [%] [^] Edit: that last one originally came out as the birthday cake smiley [^]. Edit 2: for some reason, I can't seem to use LIKE '%[^]%' - eventhough LIKE '%[%]%' works fine for me - weird! The ^ character is only a wildcard within a pattern, so you need to add brackets only if you want to use it as the first character in a pattern.