Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    New Line Statement

    Hello Everyone,

    I have a question on one of the oracle table that holds the value like below
    Y
    Y
    L

    Hex

    Y. Y. L

    I am trying to write a select statement to check the Second value is Y..

    Could you please help...

    Thanks in advance.
    Lalitha

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    487
    As it has been a week with no replies, i suspect this may be due to confusion about what you are asking. I have no idea what is being asked.

    The subject mentions "new line". Why is this mentioned?

    Why is "Hex" floating in the middle of the question?

    Are the Y & L values in different rows, different columns in the same row, or something else?

  3. #3
    Join Date
    Nov 2012
    Posts
    2
    Thanks for the response.

    Sorry for the confusion.. One column from the table hold the value like below for each row.

    Y
    Y
    L


    When I opened to view the column had two tabs in TOAD one text and the other in HEx.

    Thanks
    Lalitha

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    I know, this is rather old now, but - someone might be interested in it. (Though, it is better to ask Oracle related questions in the Oracle forum - you'd get answers much sooner).

    Anyway: I created a table and put several records in it. This is the output - note the DUMP function I used; list of numbers represents ASCII codes of these characters.
    Code:
    SQL> select col, dump(col) dmp from test;
    
    COL                  DMP
    -------------------- --------------------------------------------------
    Y                    Typ=1 Len=7: 89,13,10,89,13,10,76
    Y                                    -- -- 
    L                                    these are CHR(13) (carriage return) 
                                               and CHR(10) (line feed)
    
    A                    Typ=1 Len=10: 65,13,10,66,13,10,67,13,10,68
    B
    C
    D
    
    A                    Typ=1 Len=4: 65,13,10,89
    Y
    
    
    SQL>
    Now that we know that your "new line" consists of both CHR(13) and CHR(10), we'll replace them by an empty string:
    Code:
    SQL> select col, replace(replace(col, chr(10), ''), chr(13), '') col1 from test;
    
    COL                  COL1
    -------------------- --------------------
    Y                    YYL
    Y
    L
    
    A                    ABCD
    B
    C
    D
    
    A                    AY
    Y
    
    
    SQL>
    Finally, find the second character, the one you're interested in:
    Code:
    SQL> select
      2    col,
      3    substr(replace(replace(col, chr(10), ''), chr(13), ''), 2, 1) second_char
      4  from test;
    
    COL                  S
    -------------------- -
    Y                    Y
    Y
    L
    
    A                    B
    B
    C
    D
    
    A                    Y
    Y
    
    
    SQL>

Posting Permissions

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