Removing Tab character from source data

Question

Removing Tab character from source data

Chetan V

SSChasing Mays

Points: 656
More actions
September 22, 2010 at 9:37 am

#145398

Hi All,
I am moving data from SQL table to a fixed width flat file. I have trimmed a few columns to remove tabs and spaces.
I have found that even after trimming, tabs are being written into the fixed width flat file which actually increases size of that record.
Is there any work around for this? Any information would be of great help.:-)
Thanks in advance,
Chetan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

Are the tabs in the source SQL data, or are they being added on the way to the output file?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Chetan V SSChasing Mays Points: 656 More actions · Answer 2

Phil,

There are tabs in the source SQL table data. I want to clean these tabs and extra spaces (Transformations) before moving data to the fixed width flat file.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

You may use the Derived Transformation task.

Option 1:Functions such as REPLACE, LEN, SUBSTRING might prove useful

OR

Option 2: Use Script Component in Transformation Mode or Source Mode and use the Trim() method available.

Raunak J

theSuda SSC Eights! Points: 887 More actions · Answer 4

Ref:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18281

You can use REPLACE(<<String>>,char(9),'') and then trim it again just in case 😀

Edit: CHAR control characters

Tab: char(9)

Line feed: char(10)

Carriage return: char(13)

_____________________________________________________________

[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]

Craig Lucas SSC Veteran Points: 242 More actions · Answer 5

I use this function to clean tabs, new lines, etc from a string. You can try it:

ALTER FUNCTION [Processing].[fnFormatString](@s varchar(200))

RETURNS varchar(200)

AS

BEGIN

RETURN ISNULL(

LTRIM( RTRIM(

REPLACE(

REPLACE(@s, CHAR(0), ''),

CHAR(10) ,'')

, CHAR(13), '')

, CHAR(9), '')

))

,'')

END

Chetan V SSChasing Mays Points: 656 More actions · Answer 6

Thanks All.

I got the solution,

REPLACE([Column0],"\t"," ") will remove the tabs from source column [Column0] data.;-)