October 27, 2003 at 1:56 am
Hi all,
I've got a varchar column containing unprintable characters that is causing problems at the front end of the application. I have the list of the characters that I need to remove in hexadecimal notation.
Does anybody know of a way I can search for and remove these pesky critters from the column?
Cheers
October 27, 2003 at 2:09 am
Have you tried REPLACE?
UPDATE MyTable
SET MyColumn = REPLACE(MyColumn,
CHAR(10),
'')
Fill in the correct integer value for the not-printable character to replace.
October 27, 2003 at 5:28 am
I found the ASCII command useful when I had a similar problem. See BOL:
ASCII, code converted to a character
BOL gives some code to loop through a text string and print the ASCII code of each character in the string. This might prove helpful for finding unprintable characters you are looking for.
The code is:
Use ASCII and CHAR to print ASCII values from a string
This example prints the ASCII value and character for each character in the string New Moon.
SET TEXTSIZE 0
-- Create variables for the character string and for the current
-- position in the string.
DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
GO
Where you get ASCII values of say 31 or less these may well be the unprintable characters.
Edited by - zhesley on 10/27/2003 05:31:14 AM
October 27, 2003 at 5:29 am
Deleted duplicate post.
Edited by - zhesley on 10/27/2003 05:30:15 AM
October 28, 2003 at 12:23 am
Hi all,
Thanks for that. I wrote the below to identify my problem rows. When I was happy I was going to run the update statement thats currently commented out. However I'm getting results that are slightly confusing and I'm guessing its due to my lack of understanding of what char(0) is.
Illumination would be great. Also if this code is tragically ludicrous (or ludicrously tragic) then don't worry about hurting my feelings.
Cheers
declare @int int
set @int=0
create table #unprintable
(Code int,
custid bigint,
email varchar(100))
while @int<=31
begin
/*
update customer
set email = REPLACE(emailaddress,char(@int),'')
*/
insert into #unprintable
select @int,custid,email from customer
where email like '%'+char(@int)+'%'
print @int
set @int=@int+1
end
select * from #unprintable
October 28, 2003 at 1:40 am
char(0) is a NULL.
If you go to:
you can find out what all these characters are.
October 28, 2003 at 1:40 am
duplicate deleted
Edited by - zhesley on 10/28/2003 01:40:33 AM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply