SQL Server Performance

Issue with LIKE, checking existance of invalid chars, etc

Discussion in 'SQL Server 2005 General Developer Questions' started by itjstagame, Mar 4, 2008.

  1. itjstagame New Member

    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.
  2. Adriaan New Member

    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.

Share This Page