Removing New line character from a field

mac4rfree · #1 (**permalink**) 06-07-10, 10:49

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

Lenny77 · #2 (**permalink**) 06-07-10, 10:53

You can use:

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

Lenny

mac4rfree · #3 (**permalink**) 06-07-10, 10:56

Still having the issue Lenny...

Quote:

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.

ARWinner · #4 (**permalink**) 06-07-10, 10:58

replace(replace(source_string,chr(13),''),chr(10), '')

Andy

Lenny77 · #5 (**permalink**) 06-07-10, 11:05

Quote:

Originally Posted by mac4rfree

Still having the issue Lenny...

Where '' is 2 quotes, not a double quote....

Lenny

mac4rfree · #6 (**permalink**) 06-07-10, 11:10

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....

Quote:

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.

Quote:

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.

Lenny77 · #7 (**permalink**) 06-07-10, 12:33

Quote:

Originally Posted by mac4rfree

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

Internet Services

Web Development

Digital Marketing

Consumer Tech