New Line Statement

Lalitha_s · #1 (**permalink**) 11-21-12, 00:45

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

papadi · #2 (**permalink**) 11-28-12, 15:09

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?

Lalitha_s · #3 (**permalink**) 11-28-12, 15:17

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

Littlefoot · #4 (**permalink**) 12-31-12, 01:58

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>

Internet Services

Web Development

Digital Marketing

Consumer Tech