Special Characters in a table

• pjesrani

  Say Hey Kid

  Points: 695

  More actions

  September 9, 2008 at 11:40 am

  #123504

  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

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  September 9, 2008 at 12:35 pm

  #869237

  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