September 11, 2012 at 5:03 am
I have a table imported from excel
It has three columns, fname, lname, refno, all three of varchar (255)
There are some undesired characters in them. I need to get rid of these, ie retain ONLY alphabetic and I need a simple way of doing it. Any takers?
September 11, 2012 at 5:11 am
SSIS is your best friend for this type of thing. You can actually run a Data Flow task and do a Derived Column Transformation (use a REPLACE()) to get rid of all the weird non-alpha characters.
September 11, 2012 at 8:55 am
Try this:
CREATE FUNCTION dbo.testfunction (@input varchar(500))
RETURNS varchar(500)
BEGIN
DECLARE @output AS varchar(500)
DECLARE @i AS int
SET @i = 1
SET @output = ''
WHILE @i <= LEN(@input)
BEGIN
IF ASCII(SUBSTRING(@input, @i, 1)) BETWEEN 65 AND 90 OR (ASCII(SUBSTRING(@input, @i, 1)) BETWEEN 97 AND 122)
SET @output = @output + SUBSTRING(@input, @i, 1)
ELSE
SET @output = @output + ' '
SET @i = @i + 1
END
RETURN @output
END
Above creates a custom function.
Then you do this
SELECT dbo.testfunction(fname) AS a, dbo.testfunction(lname) AS b, dbo.testfunction(refno) AS c
INTO MYOTHERTABLE
FROM @MYTABLE
And now, your other table has been edited with the criteria you want
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply