All Forums Database
visakhcr 46 posts Joined 11/07
11 Mar 2008
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?

Regards, BB
dnoeth 4628 posts Joined 11/04
11 Mar 2008

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

Dieter

visakhcr 46 posts Joined 11/07
11 Mar 2008

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?

Regards,
BB

joedsilva 505 posts Joined 07/05
11 Mar 2008

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)

visakhcr 46 posts Joined 11/07
11 Mar 2008

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

Regards,
BB

joedsilva 505 posts Joined 07/05
12 Mar 2008

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 tableINSERT INTO MYTABLE_TMPWITH 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 RECTBLWHERE STR NOT LIKE '%' || x'0a' || '%';

visakhcr 46 posts Joined 11/07
12 Mar 2008

The following SQL works almost fine (for selection)selectcasewhen position(x'0A' in col_name) > 0then 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_nameendfrom 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?

Regards,
BB

visakhcr 46 posts Joined 11/07
12 Mar 2008

i ran the followingupdate table_name set col_name =(casewhen position(x'0A' in col_name) > 0then 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_nameend)Query executes fine. but stiil, the line feeds are present. Looks like it never updated at all....help pls..

Regards,
BB

visakhcr 46 posts Joined 11/07
13 Mar 2008

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?

Regards,
BB

joedsilva 505 posts Joined 07/05
13 Mar 2008

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

visakhcr 46 posts Joined 11/07
14 Mar 2008

hi joe..it didnt throw any error..just ran successfully..but when i queried the table,the line feeds were still present...anyway, i will check it once more..

Regards,
BB

Pierre 10 posts Joined 10/04
30 Oct 2008

Hi,i am coming late ...how in fact did you do to replace the LF characters ?it looks like one or two answers remained "off"thanks for details,Pierre

visakhcr 46 posts Joined 11/07
31 Oct 2008

Hi Peirre..what i did was just a crude method..something similar to what we call brute-force..my prob was that i was having a column which was having LF in between..the column is a char(60)...so what i did finally was to just check each character and replace it with a space if it was a line feed...the sql can be found in a previous reply..

Regards,
BB

You must sign in to leave a comment.