Help! Data format in Select...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Help! Data format in Select...

  1. #1
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    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!
    Hemant

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Thanks Jmodic!

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

    Thanka again...
    Hemant

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    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!
    Hemant

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hey thanks Jmodic!

    You're the man!
    Hemant

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Why not just -

    select '-'||WSTEXT from table1;

    or am I missing something?

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    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.

Posting Permissions

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


Click Here to Expand Forum to Full Width