1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with substring

Discussion in 'SQL PL/SQL' started by jagadekara, Nov 12, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    In fnd_flex_values_vl table in compiled_value_attributes column for flex_value='65511' we can see YYENN

    But when we copy that value and paste it some where then it shows like below.

    "Y
    Y
    E
    N
    N"

    So I need 'E' in out put. How can I get it?

    I tried it by using substr. but not succeeded.
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    This is because the field contains control characters, namely line feed "chr(10)" - that's why it appears split into multiple lines when copied to an editor.

    One way to check the content and retrieve the character is by using the following:

    Code (SQL):
    WITH pos AS
      ( SELECT level n FROM dual CONNECT BY level <=200 )
    SELECT x.compiled_value_attributes,
      ascii(SUBSTR(x.compiled_value_attributes,pos.n,1)) ch,
      SUBSTR(x.compiled_value_attributes,pos.n,1)
    FROM fnd_flex_values_vl x, pos
    WHERE flex_value_id= <flex_value_id>
    AND n <= LENGTH(x.compiled_value_attributes);
     
    jagadekara likes this.
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Brother,

    First replace the new line(enter) character with null then use substring on that.

    For to replace new line character : replace (compiled_value_attributes,chr(10),'')



    Regards
    Sambasiva Reddy.K
     
    jagadekara likes this.
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks all,

    Finally I used like below.

    substr(replace (compiled_value_attributes,chr(10),''),3,1)

    Now it's working fine.
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Alternately, if the position of the 'E' is not fixed, you could use TRANSLATE to remove both the CHR(10) as well as the Y and N values:

    Code (Text):
    SELECT 'Y' || CHR(10) || 'Y' || CHR(10) || 'E' || CHR(10) ||
           'N' || CHR(10) || 'N' AS test_text
    FROM   dual;

    TEST_TEXT
    ---------
    Y        
    Y        
    E        
    N        
    N        

    SELECT TRANSLATE('Y' || CHR(10) || 'Y' || CHR(10) || 'E' || CHR(10) ||
           'N' || CHR(10) || 'N', 'EYN' || CHR(10), 'E') AS trans_text
    FROM   dual;  

    TRANS_TEXT
    ----------
    E
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Matthew...

    In my system "Say Thanks" button is not working...