Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Removing New line character from a field

    Hi Guys,

    Somehow one of my column is having a new line character as its data. I want to show it in the single line. This is causing me an issue.

    Can you please let me know how to remove the New line character.

    I am pasting my output of my query which i ran it from the server.

    Code:
    crd02:/clocal/fastcar/user/fcfmetl $ ARGET_HISTORY_VW TVW where I_TRGT_HIST_SAN = 21443 with ur"                   <
    
    X_TRGT_COMNT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        75 PERCENT OF STATUS
    REALIGN .0359 FROM PHB20
    Thanks for your help in advance.

    Regards,
    Magesh

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can use:

    Replace (your_string, X'0d', '')

    Lenny

  3. #3
    Join Date
    Jul 2009
    Posts
    58
    Still having the issue Lenny...

    db2 "select replace(X_TRGT_COMNT,X'0d','') from FCFM.TARGET_HISTORY_VW TVW where I_TRGT_HIST_SAN = 21443 with ur"

    1

    ----------------------------------------------------------------------------------------------------------
    75 PERCENT OF STATUS
    REALIGN .0359 FROM PHB20


    1 record(s) selected.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    replace(replace(source_string,chr(13),''),chr(10), '')

    Andy

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by mac4rfree View Post
    Still having the issue Lenny...
    Where '' is 2 quotes, not a double quote....

    Lenny

  6. #6
    Join Date
    Jul 2009
    Posts
    58
    Andy ,, that did the trick

    @Lenny,, I think the hex value for new line is X'0A'.. I tried the below query still not able to get the result which i wanted but andy solution was on target....

    db2 "select replace(X_TRGT_COMNT,X'0A','') from FCFM.TARGET_HISTORY_VW TVW where I_TRGT_HIST_SAN = 21443 with ur"

    1
    ---------------------------------------------------------------------------------------------------------------
    REALIGN .0359 FROM PHB20 1 record(s) selected.
    db2 "select replace(replace(X_TRGT_COMNT,chr(13),''),chr(10), '') from CFM.TARGET_HISTORY_VW TVW where I_TRGT_HIST_SAN = 21443 with ur"

    1
    -----------------------------------------------------------------------------------------------------------------
    75 PERCENT OF STATUSREALIGN .0359 FROM PHB20
    1 record(s) selected.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by mac4rfree View Post
    Andy ,, that did the trick

    @Lenny,, I think the hex value for new line is X'0A'.. I tried the below query still not able to get the result which i wanted but andy solution was on target....
    Maybe. For mainframe is X'0D', for other systems could be different.
    Lenny

Posting Permissions

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