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.

embedded control characters

Beyond my control: I am finding control characters (likely tab) is
making its way into address fields of our operational system. This is
messing me up when I load the data into our warehouse w/ BCP (fields
get shifted).
Is the a nifty way to strip control characters from data?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> Beyond my control: I am finding control characters (likely tab) is
> making its way into address fields of our operational system. This is
> messing me up when I load the data into our warehouse w/ BCP (fields
> get shifted).
> Is the a nifty way to strip control characters from data?

UPDATE tbl
SET col = replace(col, char(9), ' ')
WHERE col LIKE '%' + char(9) + '%'

You could have to nest replace, if there are more characters you want
to kill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Embedded Code Executing SQL

I'm trying to execute a stored procedure in the header section of a report
(so I can't use a dataset in the data tab) using the shared datasource of the
report. Does anyone have a simple example executing SQL from the embedded
code of a report?Bryan,
You can use the objects from the detail section to show in the header
or...try to have a select statement in the code behind function.
if u are okay with the first line, just pop up again, i will check and give
u the code
"Bryan" wrote:
> I'm trying to execute a stored procedure in the header section of a report
> (so I can't use a dataset in the data tab) using the shared datasource of the
> report. Does anyone have a simple example executing SQL from the embedded
> code of a report?

Embedded code didnt work

In the code tab of Report Properties window, I pasted the following VB code:

Public Function Divide(Numerator As Double, Denominator As Double)
If Denominator = 0 Then
Return 0
Else
Return Numerator/Denominator
End If
End Function

But when I tried to reference the above function using "Code." in my expression, I saw an "Unrecognized Identifier" when I put my mouse onto this function in the expression edit window. Why? What I did is just copy the above function to the code tab and then start to reference it in the expression, what did I missed? Thanks for your opinion.

Change your function to:

Function Divide(Numerator As Double, Denominator As Double) As Double
If Denominator = 0 Then
Return 0
Else
Return Numerator/Denominator
End If
End Function

When you call it, use:

Code.Divide(10, 5)

email notifications scheduled jobs

In the notifications tab of the job I have e-mail operator selected. Click on the box with the elipses to enter the email addresses. It will let me enter 2 email addresses in the e-mail name field. It looks like the field has a limited length...is there a way to manually enter several addresses?http://www.sqlmag.com/Article/ArticleID/21450/sql_server_21450.html
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=31