Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Belgium
    Posts
    48

    Unanswered: querying with / and numbers

    Hi,

    I would like to do a query, the result should be all the rows that contain a / and numbers behind the slash.

    so like this /5483 or /122 and not / 123 or /f1d2 or /_82

    how can you do this with sql?

    thanks.

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    Hope this helps.

    select <the columns you want>
    from table_name
    where
    column_name like '%/1%'
    or column_name like '%/2%'
    or column_name like '%/3%'
    or column_name like '%/4%'
    or column_name like '%/5%'
    or column_name like '%/6%'
    or column_name like '%/7%'
    or column_name like '%/8%'
    or column_name like '%/9%'
    or column_name like '%/0%'

  3. #3
    Join Date
    Dec 2003
    Location
    Belgium
    Posts
    48
    thanks edwin,

    I thought something like that would be possible, but isn't their some function that can see if you are using numbers or something. Then you don't need so much where clause.

    greets

  4. #4
    Join Date
    Apr 2002
    Posts
    56
    To my knowledge there is no wildcard character for a number.

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    You could always write your own function, and then use that in the where clause.

    Select *
    From table
    Where Is_Number(column) = 1

    if your column has the format \#### then you want to strip the \ off

    so use Is_Number(substr(column,2,length(column))) = 1

    Code:
    create or replace function is_number(p_input varchar2) return number
    -- Function returns 1 if the input is a number, 0 if not.
    is
    
    lv_num     number;
    
    begin
    
       lv_num := p_input;
       return 1;
       
    exception
       when others then
          return 0;
    end;
    HIH

Posting Permissions

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