How to remove unwanted characters during ETL?

Can unwanted characters (e.g. control codes) be replaced or removed in varchar fields during extraction inside DTS package?

SQL Server/SSIS 2005.

Thanks, Andrei.

Inside of an SSIS package, yes. DTS is old news now! You can use the REPLACE() function along with a CHAR() function in a derived column transformation.

To remove tabs: REPLACE([column],char(9),"")|||

Thanks, Phil!

This is the way to go.

Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.

2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Regards, Andrei.

|||

Andrei Kuzmenkov wrote:

Thanks, Phil!

This is the way to go.

Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.

2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Regards, Andrei.

CHAR() is too supported, it's just not in the drop down list. You can use it inside the replace function, just as I noted. You just have to type it in there. You could always write 31|||

Andrei Kuzmenkov wrote:

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Yes, you can create Script Transform and easily call custom function from there. Or if it is one-time code, you may simply place it in the Script Transform code as well.

Note that the custom assembly needs to be placed to GAC and because of to limitation of VSA we use for scripting it also should be in Windows\Microsoft.NET\Framework\v2.0.50727 (this is only needed for design time).

|||

In VS2005 I'm getting "The Function CHAR was not recognized".

How to remove tabs from a field?

Is there any way to remove tabs from text fields? I see tabs at the end when I import the data in Excel.
Appreciate any helpSure, in the view that you are extracting use the Replace function, something like:CREATE VIEW vThingie
SELECT thingieId, Replace(thingieText, Char(9), ' ') AS d
FROM thingie
WHERE thingieId < 100...or something like that!

-PatP

how to quick query duplicate records?

any idea?

quick query duplicate records (speicifed fields.value are same) using T-SQL?

depends on how many columns you've got in the table but as an example:

say you have a table called Customer with one column called CustomerID then this simple query will tell you.

SELECT CustomerID, COUNT(*)

FROM Customer

GROUP BY CustomerID

HAVING COUNT(*) > 1

ORDER BY 2 DESC

|||

Thanks your reply what i want is

display duplicate records depends on multi-column

regards

|||

just add the extra columns in to the SELECT and GROUP BY clauses and it will work for multiple columns.

even better, can u post an example of the data that you are wanting to look for duplicates in?

|||

At the same time if you table own identity field you could use another query. Keeping on mind identity value you drop the one own less value.

I'm sorry but I don't have that query

|||

Because i want to remove duplicate records,so have to display first i think

I found an article

http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp

I think it's cool, sharing

How to Query to get the Numeric fields Only in the Table

hi Good Day everyone.

how can i query numeric values in the specific fields for example EmployeeID only.

but the field EmployeeID has a datatype of varchar.

for example :

EmployeeID -> Field

001

002

a

b

c

how do i query 001 and 002 only. tnx

here it is,

Code Snippet

Create Table #employee (

[EmployeeID] Varchar(100)

);

Insert Into #employee Values('001');

Insert Into #employee Values('002');

Insert Into #employee Values('a');

Insert Into #employee Values('b');

Insert Into #employee Values('c');

Insert Into #employee Values('1E2');

Insert Into #employee Values('.');

Insert Into #employee Values('$');

Insert Into #employee Values('E0');

Select

*

from

#employee

Where

Isnumeric([EmployeeID]) = 1

And [EmployeeID]not like '%[^0-9]%'

|||Thanks Manivannan.D.Sekaran it helps me a lot.

How to query out these annoying New line characters

Hello, when I imported data from ACCPAC to MS SQL Server 2000, I noticed
there were fields with a SQUARE character appended to the end of the values.
I am guessing these are NEW LINE CHARACTERs.
My question is how can I query these characters out because I want to delete
them. I do not want to go one row at a time and erase it!
Thanx in advance
Jiro Hidaka
Programmer for Medisca
PharmaceutiqueA square is unlikely to be a new line character.
Can you figure out what the ascii value is?
SELECT ASCII(SUBSTRING(col, position_where_square_is, 1));
--UPDATE tbl SET col = REPLACE(col, CHAR(result_from_above));
"Jiro" <medisca@.newsgroups.nospam> wrote in message
news:F558657A-D1DB-4083-9C0B-11F01244F366@.microsoft.com...
> Hello, when I imported data from ACCPAC to MS SQL Server 2000, I noticed
> there were fields with a SQUARE character appended to the end of the
> values.
> I am guessing these are NEW LINE CHARACTERs.
> My question is how can I query these characters out because I want to
> delete
> them. I do not want to go one row at a time and erase it!
> Thanx in advance
> --
> Jiro Hidaka
> Programmer for Medisca
> Pharmaceutique|||What are you looking for - carriage return characters, line feed characters
or both?
char(10) = line feed
char(13) = carriage return
declare @.str varchar(256)
set @.str ='this is
a new line'
select replace(@.str, char(10) + char(13), '')
ML
http://milambda.blogspot.com/|||> select replace(@.str, char(10) + char(13), '')
Actually, most times that pair will be inserted in the opposite order, 13
first, then 10.|||Yes, of course! I was a bit fast with copy/paste.
Thanks for pointing it out!
ML
http://milambda.blogspot.com/