Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Arrow Unanswered: Search for non alphabatical values

    I have the next sqlstatement for sybase and sqlserver:
    SELECT SFIENAME
    FROM DBA.SFIELD
    WHERE SFIENAME LIKE '%[^a-zA-Z0-9_]%';
    It will return the rows where sfiename has a character that does not confirm one of the next rules:
    1 Character must be between a and z
    2 Character must be between A and Z
    3 Character must be between 0 and 9
    4 Character must be _

    How can I do this for Oracle: I know for the underscore. But range can only be done with between:
    SELECT SFIENAME
    FROM ISH.SFIELD
    WHERE SFIENAME LIKE '%\_%' ESCAPE '\';

    beforehand thanx

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle 10G can handle regular expressions (check docs for how), but prior to 10G you have to do it differently, e.g.

    SELECT SFIENAME
    FROM DBA.SFIELD
    WHERE TRANSLATE (UPPER(SFIENAME), '#0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_', '#') IS NOT NULL

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You will have to be using Oracle 10g to do something like the regex matching like you want.

    I'm not a regex guru, but I think it would look something like this:

    Code:
    SELECT SFIENAME
    FROM DBA.SFIELD
    WHERE REGEXP_LIKE (SFIENAME, '[^a-zA-Z0-9_]' );
    If you need this pre-10g, you would have to create a function to process the rules like you specified and your WHERE clause would be:

    ... WHERE my_function( SFIENAME );

    and my_function would return a BOOLEAN value based on some processing.
    JoeB
    save disk space, use smaller fonts

  4. #4
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Arrow Thanx

    Both thanx. I just use the straightforward solution from andrewst.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This link shows how to use regexp before 10g. Andrew's solution is probably best for your example but if you have more complex matching then use this...

    http://www.stormloader.com/yonghuang...cleRegExp.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •