Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    40

    Unanswered: Checking for ~ ^ and single Quote in a field

    Hello all

    I want to write a select statement which can check a ~ ,^ and single Quote in a coloumn in a table

    Ex : Table Test ...

    item varchar(20)
    desc varchar(30)
    pro varchar(50)

    From this table i want all the item's where item contain ~ or ^ and single quote in the values...so please give me example also

    thanks
    Suryadevara

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

    Re: Checking for ~ ^ and single Quote in a field

    I think this what you want:

    SQL> r
    1* select * from joel_table

    C1 C2
    ---------- -----
    1 ki~op
    2 kkk

    SQL> select * from joel_table
    2 where c2 like '%~%';

    C1 C2
    ---------- -----
    1 ki~op

    SQL> insert into joel_table values(3,'k''re');

    1 row created.

    SQL> select * from joel_table;

    C1 C2
    ---------- -----
    1 ki~op
    2 kkk
    3 k're

    SQL> select * from joel_table
    2 where c2 like '%''%';

    C1 C2
    ---------- -----
    3 k're

    SQL>
    Joel Pérez

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Checking for ~ ^ and single Quote in a field

    You could use LIKE:

    where (item like '%~%' or item like '%^%' or item like '%''%')

    or instr:

    where (instr(item,'~') > 0 or instr... etc.)

    or translate:

    where translate(item,'~^''','XXX') != item

    The last one is the shortest and neatest, but also the hardest to understand I suspect!

Posting Permissions

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