December 1, 2009 at 2:40 am
HI,
I have a employee profile tablewith 5000 rows. IN the firstname and lastname field few junk chacters been imported to the table in sql server. Is there any script to find these junk chracters so that it can be taken care.
Cheers,
Got an idea..share it !!
DBA_Vishal
December 1, 2009 at 2:44 am
Hi,
What you mean by the JUNK characters,
Give some samples of those characters, its can help to offer some advice
December 1, 2009 at 7:03 am
It might also help to know what collation you're using.
December 1, 2009 at 8:20 am
Here's a slighly modified version of a function that I use for something like this. It requires a Tally Table. See the article on Tally tables in my signature for more on what those are. Note: This is probably not the most optimal way to do this.
CREATE FUNCTION SCA_TallyClean(
@a varchar(5000))
RETURNS varchar(5000)
AS
BEGIN
DECLARE @b-2 varchar(5000)
SET @b-2 = '' -- Initialize @b-2
SET @a = REPLACE(REPLACE(@A,char(10),' '),char(13),' ') -- Remove Line Feed / Carriage Returns (The below would have removed them,
-- but I wanted to replace them with spaces for readability.)
SELECT @b-2 = @b-2 + SUBSTRING(@A,N,1)
FROM Tally
WHERE N <= DATALENGTH(@A)
AND
SUBSTRING(@A,N,1) LIKE '[a-zA-Z0-9. ,]' -- Remove everything but letters, numbers, spaces, period and comma.
RETURN REPLACE(REPLACE(@B,' ',' '),' ',' ') -- Removes some double spaces.
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply