3 Replies Latest reply: Dec 28, 2012 12:12 AM by ranit B RSS

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

    971653
      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
          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
            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
              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.