Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Posts
    15

    Unanswered: how to select records which contain non-digit characte?

    Hi,
    How to implement a sql query to find out records which contains non-digit characters? (For example: I want to validate the ssn column and find out those unqualified records)
    Is there any way to do this without using function?

    Thanks for the help in advance.

    Tien-Chih Wang

  2. #2
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: how to select records which contain non-digit characte?

    You can do it with LIKE in the SQL Statement.
    Joel Pérez

  3. #3
    Join Date
    Feb 2002
    Posts
    15

    Re: how to select records which contain non-digit characte?

    Originally posted by joelperez
    You can do it with LIKE in the SQL Statement.
    can you give me detail info about how to do it by 'Like'.

    select * from table
    where (ssn like '%a%') and (ssn like '%b%') and .....so on ??
    Any better solution

  4. #4
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: how to select records which contain non-digit characte?

    yes, so because if you use a function you have to make call(s) to PL/SQL engine to resolve it.
    Joel Pérez

  5. #5
    Join Date
    Aug 2001
    Posts
    66

    Re: how to select records which contain non-digit characte?

    Prior to 10G's regular expression support I recommend you check out TRANSLATE or LTRIM / RTRIM functions for this kind of thing, e.g.

    SELECT column_name
    FROM table_name
    WHERE LTRIM (column_name, '0123456789') IS NOT NULL;

    However such a lookup cannot be answered by a unique or range scan of a conventional index on column_name - if this is something you do a lot and the column values are suitable selective you might consider creating a function-based index on a similar expression to answer such a query.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  6. #6
    Join Date
    Feb 2002
    Posts
    15

    Re: how to select records which contain non-digit characte?

    Thanks. Working perfectly.

    Tien-Chih Wang

Posting Permissions

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