Removing TABS & NEWLINE from text field

• Tim Morrison

  SSC Eights!

  Points: 894

  More actions

  February 10, 2006 at 3:24 am

  #93378

  Hi People,

  I'd really appreciate some help here.

  When I run a query on one of the DBs I work with, and display the results to GRID, it all looks lovely.  I save the report and import it to EXCEL and it all goes haywire.  When I open the .rpt file in a text editor, the data is mis-aligned.

  There's obviously TABS and NEWLINE (CRLF) hidden in there which throws the "Data Import" process in EXCEL.

  Does anyone have an exaple of how I can strip this stuff out, or failing that, a way to export to EXCEL that won't suffer the same fate?

  Cheers all,

  Tim

• Jesper-244176

  SSCertifiable

  Points: 7142

  More actions

  February 10, 2006 at 5:18 am

  #620072

  When you say "text field", do you mean a field of datatype varchar? Then use something like this:

  select replace(replace(colname, char(9), ''), char(13) + char(10), '')

  from table

  If you mean a field of datatype text, it's more complicated. The statement below will return the first 8000 characters without newlines and tabs:

  select replace(replace(cast(substring(colname, 1, 8000) as varchar), char(9), ''), char(13) + char(10), '') from table

• Tim Morrison

  SSC Eights!

  Points: 894

  More actions

  February 10, 2006 at 6:28 am

  #620081

  Thanks Jesper, you is the man.

  I knew of the REPLACE function, but hadn't thought about the fact that tab & newline have char values.

  I really appreciate your help, many thanks and have a great weekend!

  

  Tim