September 9, 2008 at 11:40 am
Hi Folks,
I have a problem. I have a table which has some special characters in some of its fields. These special charactes can only be seen through the enterprise manager. Currently i use a cursor to remove these character. Now the problem i have to use this cursors on multiple tables. which is taking up a lot of time and manual intervention. Is there a way i can do this to ssis. I have tried using while loop but it is taking up a lot of time. Mentioned below is the cursor that we have. Please help.
DECLARE @mycode INT
CREATE TABLE #badcodes(badcode INT)
SET @mycode=0
WHILE @mycode<=255
BEGIN
IF (@mycode BETWEEN 0 AND 47) OR (@mycode BETWEEN 58 AND 64)
OR (@mycode BETWEEN 58 AND 64) OR (@mycode BETWEEN 91 AND 96)
OR (@mycode BETWEEN 123 AND 255)
INSERT INTO #BADCODES VALUES(@mycode)
SET @mycode=@mycode+1
END
go
--select * from #badcodes
declare @acc varchar(10),
@othPh1 varchar(10),
@othPh2 varchar(10),
@FName VARCHAR (35),
@LName VARCHAR (35),
@HomePhone VARCHAR(10),
@BusinessPhone VARCHAR(10),
@OsacClient VARCHAR(8),
@OsacCName VARCHAR(40),
@State Char(2)
DECLARE CUR_COLNAME CURSOR FOR SELECT Account,OtherPhone1,OtherPhone2,FName,Lname,HomePhone,BusinessPhone,OsacClient,OsacCname,State FROM
Tablename
OPEN CUR_COLNAME
FETCH NEXT FROM CUR_COLNAME INTO @acc, @othPh1,@othPh2,@FName,@Lname,@HomePhone,@BusinessPhone,@OsacClient,@OsacCName,@State
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #badcodes
SET @othPh1= REPLACE(rtrim(@othPh1),CHAR(badcode),''),
@othPh2= REPLACE(rtrim(@othPh2),CHAR(badcode),''),
@FName= REPLACE(rtrim(@FName),CHAR(badcode),''),
@LName= REPLACE(rtrim(@LName),CHAR(badcode),''),
@HomePhone= REPLACE(rtrim(@HomePhone),CHAR(badcode),''),
@BusinessPhone= REPLACE(rtrim(@BusinessPhone),CHAR(badcode),''),
@OsacClient= REPLACE(rtrim(@OsacClient),CHAR(badcode),''),
@OsacCName= REPLACE(rtrim(@OsacCName),CHAR(badcode),''),
@State= REPLACE(rtrim(@State),CHAR(badcode),'')
update tablename set OtherPhone1 =@othPh1,
OtherPhone2 =@othPh2,
FName =@FName,
LName =@Lname,
HomePhone =@HomePhone,
BusinessPhone = @BusinessPhone,
OsacClient =@OsacClient,
OsacCName =@OsacCName,
State= @State
where Account=@acc
FETCH NEXT FROM CUR_COLNAME INTO @acc, @othPh1,@othPh2,@FName,@LName,@HomePhone,@BusinessPhone,@OsacClient,@OsacCName,@State
END
close CUR_COLNAME
deallocate CUR_COLNAME
drop table #badcodes
September 9, 2008 at 12:35 pm
This does not use SSIS, but is simpler than the cursor solution and should be efficient as it is set based:
[font="Courier New"]-- create table variable for test data
DECLARE @table1 TABLE (OtherPhone1 VARCHAR(25))
-- create numbers or tally table -- you could/should have one anyway
SELECT TOP 255
IDENTITY(INT, 0, 1) AS N
INTO
#nums
FROM
MASTER.dbo.syscolumns S1,
MASTER.dbo.syscolumns S2
-- insert test data adding a special character
INSERT INTO @table1
SELECT TOP 25
name + CHAR(N)
FROM
MASTER.dbo.syscolumns S CROSS JOIN
#nums N
WHERE
N.N BETWEEN 0 AND 47 OR
N.N BETWEEN 58 AND 64 OR
N.N BETWEEN 91 AND 96 OR
N.N BETWEEN 123 AND 255
-- show the data in the table
SELECT *, LEN(Otherphone1) AS CHARs FROM @table1
--update the table removing special characters
UPDATE @table1
SET OTHERPHONE1 = REPLACE(OTHERPHONE1, CHAR(N), '')
FROM
#nums
WHERE
N BETWEEN 0 AND 47 OR
N BETWEEN 58 AND 64 OR
N BETWEEN 91 AND 96 OR
N BETWEEN 123 AND 255
-- select the updated table to verify I removed characters
SELECT *, LEN(Otherphone1) AS CHARs FROM @table1
-- drop the nums table
DROP TABLE #nums[/font]
You could do it in SSIS as well, but I think this is easier. Numbers/Tally table idea from this article:
http://www.sqlservercentral.com/articles/TSQL/62867/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply