If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Applications & Tools > New Line Statement

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,980
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>
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On