How to avoid line feed?

Database
Enthusiast

How to avoid line feed?

I have a particular table that returns a character string...like "This is a problem" followed by a line feed.

for eg.

"This is a problem
"

I am using bteq to select this data and export to a .txt file. The linefeed is causing a problem with the exported file(format). Can someone help me to solve this?
13 REPLIES
Junior Contributor

Re: How to avoid line feed?

Hi bonchibuji,
BTEQ in REPORT mode will not export any cr/lf, but replace those non-printable charcters with blanks.

If you export using DATA mode you'll have to replace the cr/lf, the best way to achieve that are the Oracle UDF otranslate or oreplace.

Dieter
Enthusiast

Re: How to avoid line feed?

Hi Dieter,

Thanks for your reply..

I was mistaken to say that it was BTEQ.

We are having a table which contains line feed. Is there any way to replace the line feed with blanks using an update statement?

Enthusiast

Re: How to avoid line feed?

If you have line feeds only at the end of the string value, then probably this is what you need in the update statement.

TRIM( TRAILING x'0A' FROM col1)
Enthusiast

Re: How to avoid line feed?

Joe..thanks for the reply..

i already tried trim..but it doesnt seem to work...

i tried with trailing and both options...

line feed occurs before, after and in-between...pls suggest some method...

Enthusiast

Re: How to avoid line feed?

You can probably try making use of a recursive query like this .....

I am not sure if you can run an update directly using it, but should be able to insert into an intermediate table which can then be used to update the main table

INSERT INTO MYTABLE_TMP
WITH RECURSIVE RECTBL(ID, STR)
AS
(

SELECT ID, SUBSTRING(STR FROM 1 FOR POSITION(x'0a' IN STR)-1) || SUBSTRING(STR FROM POSITION(x'0a' IN STR) + 1)
FROM MYTABLE
WHERE STR LIKE '%' || x'0a' || '%'

UNION ALL

SELECT ID, SUBSTRING(STR FROM 1 FOR POSITION(x'0a' IN STR)-1) || SUBSTRING(STR FROM POSITION(x'0a' IN STR) + 1)
FROM RECTBL
WHERE STR LIKE '%' || x'0a' || '%'

)
SELECT *
FROM RECTBL
WHERE STR NOT LIKE '%' || x'0a' || '%'
;

Enthusiast

Re: How to avoid line feed?

The following SQL works almost fine (for selection)

select
case
when position(x'0A' in col_name) > 0
then substr(col_name,1,position(x'0A' in col_name)-2) || '' || substr(col_name,position(x'0A' in col_name) + character_length(x'0A'))
else col_name
end
from table_name where col_name like '%
%'

is there any way to put this in an statement? Also, the above query still returns some linefeed. any suggestions?
Enthusiast

Re: How to avoid line feed?

i ran the following

update table_name
set col_name =
(case
when position(x'0A' in col_name) > 0
then substr(col_name,1,position(x'0A' in col_name)-2) || ' ' || substr(col_name,position(x'0A' in col_name) + character_length(x'0A'))
else col_name
end)

Query executes fine. but stiil, the line feeds are present. Looks like it never updated at all....help pls..
Enthusiast

Re: How to avoid line feed?

thanks joe and dieter...

finally i was able to remove all the line feeds....

btw, i also tried it in a different method...i am attching the sql...it's replacing each character..but it didnt work..any suggestions?
Enthusiast

Re: How to avoid line feed?

It worked on my test data.... so not sure what could be the reason it didn't work for you ...

did it throw an error ? misplaced wrong characters... ???