Replacing Multiple Strings Using the REPLACE Function

I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?

Thanks,
Ridium

Ridium wrote:

I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?

Thanks,
Ridium

There isn't a simpler way, that is exactly how you should do it. Its simple and it works and I don't think its cumbersome at all. Just my opinion.

What syntax do you envisage for a REPLACE function that allows you to replace multiple strings? Also, in your example given above I can envisage it replacing the "99999" part of "-99999" and you being left with "-" which isn't what you want.

-Jamie

|||I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium
|||

Ridium wrote:

I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium

Yeah, I think that's what you'll have to do. Why is that such a problem? I honestly can't fathom how this could be less (in your words) "cumbersome". I'm interested in any ideas you may have.

Regards

Jamie

|||

Jamie Thomson wrote:

Ridium wrote:

I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium

Yeah, I think that's what you'll have to do. Why is that such a problem? I honestly can't fathom how this could be less (in your words) "cumbersome". I'm interested in any ideas you may have.

Regards

Jamie

I discovered a more convenient method. Instead of using all those REPLACE functions, just use an expression:
mycolumn == "99999" || mycolumn == "-99999" ? NULL(DT_DECIMAL,2) : (DT_CY)mycolumn

I can just add an additional "or" operation for each new term I want to search for. This is also less prone to errors.

Ridium
|||

Ridium wrote:

Jamie Thomson wrote:

Ridium wrote:

I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium

Yeah, I think that's what you'll have to do. Why is that such a problem? I honestly can't fathom how this could be less (in your words) "cumbersome". I'm interested in any ideas you may have.

Regards

Jamie

I discovered a more convenient method. Instead of using all those REPLACE functions, just use an expression:
mycolumn == "99999" || mycolumn == "-99999" ? NULL(DT_DECIMAL,2) : (DT_CY)mycolumn

I can just add an additional "or" operation for each new term I want to search for. This is also less prone to errors.

Ridium

OK, glad you found something that you're happy with. A word of warning though, use parentheses around the first argument to the conditional operator or else you could find yourself in a world of hurt.

Why do you think that is less prone to errors? And when you say "just use an expression", why would using the REPLACE function not constitute using an expression?

Regards

-Jamie

|||I meant use a conditional expression. It seems pretty obvious that something like this:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A,B,C),D,E),F,G),H,I),J,K)

Is a lot more complicated than my solution. Imagine trying to include 20 or 30 functions. This is much harder to read and understand. You could easily lose sight of what parameter goes to what REPLACE function which might cause an error.

Ridium
|||

Ridium wrote:

I meant use a conditional expression. It seems pretty obvious that something like this:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A,B,C),D,E),F,G),H,I),J,K)

Is a lot more complicated than my solution. Imagine trying to include 20 or 30 functions. This is much harder to read and understand. You could easily lose sight of what parameter goes to what REPLACE function which might cause an error.

Ridium

OK fair enough, can't argue with that. Note what I said about parentheses though!

-Jamie

Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

sql

Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

Replace Column with a calculated Column

Hello Everyone,

I'm a newbie to SSIS. While experimenting with it I've encountered an issue which I'm hoping someone of you could help me out with. I have need to make a specific transformation which as output would have to produce rows with a new calculated column that replaces single column from input. New column has different data type than input column it is replacing. I've used Derived Column Transformation (DER) to do the first part of the work - appending new column and calculating its value (based solely on value from single original column that has to be replaced). Question is how should I do second part, task of removing no longer needed column from the pipeline? I've tried in DER instead of Derived Column being added as new column, selecting Replace 'column' but as it seems it is meant to replace only column data and not column data type (what I've expected). I've also tried using Copy Column Transformation (CPYC) but as it turns out CPYC transformation just (logically) duplicates data in the pipeline with optional different allias.
There's no issue here. Just leave the original column alone in the data flow. It isn't unused, because you used it in the calculation of the new column. That's where you stop using the original column. Just leave it alone after that point.

Replace a string in an NTEXT field in sql server

I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.

NTEXT is depricated in SQL 2005 and was replaced by NVARCHAR(MAX). You can use the REPLACE in SQL 2005

http://msdn2.microsoft.com/en-us/library/ms186862.aspx

|||Can access a sql2000 table with NTEXT field as it is and replace a string in that field using SSIS2005? Please advise. Thanks.|||

As I know in SQL2000 the REPLACE will not accept NTEXT data as parameter, which brings trouble when update TEXT data. If there are less than 4000 chars in the NTEXT column, I'd suggest casting the NTEXT data to NVARCHAR(4000) data so that you can use the REPLACE function. Otherwise the replacing is really ugly. Here is a sample to update TEXT data by replacing string:

USE TempDB;
GO

SET NOCOUNT ON;

CREATE TABLE dbo.data
(
DataID INT PRIMARY KEY,
txt NTEXT -- change to TEXT
);
GO

INSERT dbo.data
SELECT 1, N'bar foodfood food har sammy'
UNION ALL SELECT 2, N'bar sammy food'
UNION ALL SELECT 3, N'bar fooblat sammy'
UNION ALL SELECT 4, N'food';

DECLARE
@.TextPointer BINARY(16),
@.TextIndex INT,
@.oldString NVARCHAR(32), -- change to VARCHAR
@.newString NVARCHAR(32), -- change to VARCHAR
@.lenOldString INT,
@.currentDataID INT;

SET @.oldString = N'food'; -- remove N
SET @.newString = N'fudge'; -- remove N

IF CHARINDEX(@.oldString, @.newString) > 0
BEGIN
PRINT 'Quitting to avoid infinite loop.';
END
ELSE
BEGIN
SELECT 'Before replacement:';

SELECT DataID, txt FROM data;

SET @.lenOldString = DATALENGTH(@.oldString)/2; -- remove /2

DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
DataID
FROM
dbo.data
WHERE
PATINDEX('%'+@.oldString+'%', txt) > 0;

OPEN irows;

FETCH NEXT FROM irows INTO @.currentDataID;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN

SELECT
@.TextPointer = TEXTPTR(txt),
@.TextIndex = PATINDEX('%'+@.oldString+'%', txt)
FROM
dbo.data
WHERE
DataID = @.currentDataID;

WHILE
(
SELECT
PATINDEX('%'+@.oldString+'%', txt)
FROM
dbo.data
WHERE
DataID = @.currentDataID
) > 0
BEGIN
SELECT
@.TextIndex = PATINDEX('%'+@.oldString+'%', txt)-1
FROM
dbo.data
WHERE
DataID = @.currentDataID;

UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString @.newString;
END

FETCH NEXT FROM irows INTO @.currentDataID;
END

CLOSE irows;

DEALLOCATE irows;

SELECT 'After replacement:';

SELECT DataID, txt FROM data;
END

DROP TABLE dbo.data;

|||

Does your solution apply only if there are less than 4000 chars in the NTEXT column? Here's my next questions: how can I find the max length of those ntext fields? But if the max length exceeds 4000, can I use nvarchar(max) in the sql2005 temp table and, after replacing my string, convert the result back to ntext in the sql 2000 source table? Can I use sql2005 for temp work and keep the final data in sql2000. Thanks.

|||You can use datalength(yourColumn) to find your ntext length.|||lori_Jay's solution looks good but has a serious flaw: the new string replacement is truncated to the old string's length after it goes into the NTEXT field. Please advise. Thanks.|||

Problem can be solved with repalcing:
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString @.newString;
with
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString -- deletes old string
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex 0 @.newString -- inserts new string