Help! Data format in Select...

[hemanr]

Hi Gurus,

I issue the following statement:

SQL>select wstext from table1;

The circuit breaker will be received and inspected for worn, damaged, and missing components.
Arc chutes will be removed and inspected for erosion
Contacts will be inspected for proper wipe and signs of deterioration.

Now I issue

SQL> select replace(WSTEXT,chr(10),chr(10)||'-')from table1;

The circuit breaker will be received and inspected
-Arc chutes will be removed and inspected for erosion
-Contacts will be inspected for proper wipe and signs of
-deterioration.

Why does the first line of output not have a '-'?

Any suggestions?

Thanks folks!

[jmodic]

Because there is no "line feed" CHR(10) character at the begining of the first line of your text. CHR(10) characters are actualy *at the end* of each line, except for the last line as can be seen from your output.

[hemanr]

Thanks Jmodic!

Is there a way I can get the '-' at the beginning of each output line?

Thanka again...

[jmodic]

Sure.

select '-' || replace(WSTEXT,chr(10),chr(10)||'-')
from table1;

If there is any chance that there coluld be CHR(10) as the first character in the first line, you should use the following to prevent two consecutive '-' characters to appear in the first line:

select '-' || replace(ltrim(WSTEXT,CHR(10)),chr(10),chr(10)||'-')
from table1;

[hemanr]

Hi Jmodic!

Thanks a deal!
Well Im no skydiver anyway...

Hey your first sequel works great but the second one does not eliminate '-'s already in the text...

I get data like:

-Breaker electrical accessory devices, if so equipped
-Electrical tests will be performed, as applicable.
--High current injection tests

Is there any modofication I need to do here?

Thanks again!

[jmodic]

select REPLACE('-' || replace(ltrim(WSTEXT,CHR(10)),chr(10),chr(10)||'-'), CHR(10)||'--', CHR(10)||'-')
from table1;

Now your next question will probably be: What if there are allready two consecutive '-' characters at the beginning of a new line? And what if there are three? Four? ..... Well, for that you would have to write a rather simple PL/SQL function that will be able to eliminate any number of consecutive '-' characters from the beginning of each line in your text.

[hemanr]

Hey thanks Jmodic!

You're the man!

[JMac]

Why not just -

select '-'||WSTEXT from table1;

or am I missing something?

[stecal]

You're missing something.

SQL> l
1* select '-'||input from supp3
SQL> /

'-'||INPUT
-----------------------------------------------------------------------------------------------
-The circuit breaker will be received and inspected for worn, damaged, and missing components.
Arc chutes will be removed and inspected for erosion
Contacts will be inspected for proper wipe and signs of deterioration.