Embedded tab Character

I have embedded tabs in a text field that I want to import to a destination table.

I was thinking I need to replace the tabs with spaces.

REPLACE(character_expression,searchstring,replacementstring) Anybody know how to specify ascii in the character expression.If there is a better way I am open to suggestions, however I do not way to remove this in the raw data but handle at transformation time. Thanks,LarryReplace ( fieldname, CHAR(10), ' ')|||

Thanks,

I beleive it is a char 9 , but I really wanted to change crlf Char(10) & CHAR(13).

Can I do this in one statement with the replace

Replace ( fieldname, CHAR(10) & CHAR(13), ' ')

Thanks,

Larry

|||

Regular expression work well to match and remove / replace string patterns.

To remove carriage return / new line combos, use a script component transform which replaces each occurrence of the pattern with an empty string.

To do so, drop a script component on the data flow canvas, selecting the desired column from the available input columns, and setting its usage type to Read/Write.

A regular expression is then used to match and remove the pattern.

Imports System
Imports

System.Data
Imports

System.Math
Imports

Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports

Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports

System.Text.RegularExpressions

Public Class ScriptMain
Inherits

UserComponent

Private

regex As Regex = New

Regex("\r\n",

RegexOptions.Compiled)

Public Overrides Sub

Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not Row.GeneratedStr1_IsNull Then
Row.GeneratedStr1 =

regex.Replace(Row.GeneratedStr1, String.Empty)
' Replace

all occurrences of pattern with empty string,
'provided input column is not null
End If
End Sub
End Class

|||

This is pretty Sweet, endless opportunity, unlimited potential.

Thanks,

Larry

|||

This is a SSIS forum, so how about a SSIS solution using REPLACE, may be easier than the Script Component route-

REPLACE(ColumnName, "\t", "")

\t is the escape sequenece for tab in a literal. This expression could be used in a Derived Column transform. Select the Replace "Column" option to clean existing columns in-place.

|||

Many ways to skin the Cat.

Can you do a crlf replace and a lf replace in the same replace statement.

There may be many line feeds and one crlf in the same text field that I am trying to clean.

Thanks,

Larry

|||

Nest the replace statements -

REPLACE(REPLACE(ColumenName, "\r\n", ""), "\n", "")

Or

REPLACE(REPLACE(ColumeName, CHAR(13) + CHAR(10), '', CHAR(10), '')

You could of course do just replace Cr and then Lf in a similar format, I just like the explicit nature of doing CrLf and Lf as units.