This discussion is archived
3 Replies Latest reply: Dec 27, 2012 10:12 PM by ranit B RSS

how to retrive the data in where clause  which is    20'

971653 Newbie
Currently Being Moderated
SELECT mkct_group_code FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code=20'

Note mkct_group_code column is varchar2(10) type.
It has values as
20
10
30
20'
30'
  • 1. Re: how to retrive the data in where clause  which is    20'
    Purvesh K Guru
    Currently Being Moderated
    968650 wrote:
    SELECT mkct_group_code FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code=20'

    Note mkct_group_code column is varchar2(10) type.
    It has values as
    20
    10
    30
    20'
    30'
    Since, the column values contain a Single Quote ('), and you are comparing character data, use as below:
    SELECT mkct_group_code  FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code='20'''
  • 2. Re: how to retrive the data in where clause  which is    20'
    Karthick_Arp Guru
    Currently Being Moderated
    968650 wrote:
    SELECT mkct_group_code FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code=20'

    Note mkct_group_code column is varchar2(10) type.
    It has values as
    20
    10
    30
    20'
    30'
    Oracle has introduced the Q notation to represent string literals. By using this you can specify your own separator.

    Example.

    If you want to pass a string as Hi it's Karthick's post

    Previously we use to do it like *'Hi it''s Karthick''s post'*

    But using the Q notation you can just do this *q'[Hi it's Karthick's post]'*. Here the Square brackets are the separators. Only thing you need to make sure is that the separator is not part of the actual string. You can specify any separator like q'|Hi it's Karthick's post|' or *q'{Hi it's Karthick's post}'* etc.
  • 3. Re: how to retrive the data in where clause  which is    20'
    ranit B Expert
    Currently Being Moderated
    Another way...
    with xx as(
        select '20' val from dual UNION ALL
        select '10' val from dual UNION ALL
        select '30' val from dual UNION ALL
        select '20''' val from dual UNION ALL --- replacing a Single-quote with 2 Single-quotes
        select '30''' val from dual UNION ALL  -- replacing a Single-quote with 2 Single-quotes
        select q'_20'_' val from dual UNION ALL  --- Q-notation
        select q'#30'#' val from dual  --- Q-notation
    )
    select val from xx
    where val = 20||chr(39);
    gives o/p :
    20'
    20'
    Ranit B.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points