Removing "special characters" from VarChar

9 Dec 2005 9:51 PM

Mike Austin

My tables are being sent data from SPSS via SAS. It appears that one of these
products is adding character(s) to my varchar columns. The character looks
like a square. If I query the table in DB Artisan, the column looks like line
feeds. IOW, the cell containing the data has one or more blank lines after
the data.
(The SAS/SPSS people claim that these characters are not in the source data,
but are being added during the insert/conversion process.)

My questions are:

1. How do I determine what the character is?
2. How do I remove these characters from my columns.

If I copy and paste the string from either Excel or the query results into
Notepad, the added character appears to be removed. That is, no line feeds or
tabs are detectable.

9 Dec 2005 10:06 PM

Trey Walpole

to find out what they are:

declare @i int, @string varchar(<size>)
set @i=1
while @i<=len(@string) begin
print substring(@string, @i, 1) + ' ' + convert(varchar,
ascii(substring(@string, @i, 1))
set @i=@i+1
end

you're right, it's probably return/line feed (char(13) and char(10)
respectively)

remove it by using REPLACE()

e.g. if char(13) and/or char(10) - otherwise use the ascii values from above
select @string = replace(replace(@string, char(13), ''), char(10), '')

Mike Austin wrote:
Show quote

> My tables are being sent data from SPSS via SAS. It appears that one of these
> products is adding character(s) to my varchar columns. The character looks
> like a square. If I query the table in DB Artisan, the column looks like line
> feeds. IOW, the cell containing the data has one or more blank lines after
> the data.
> (The SAS/SPSS people claim that these characters are not in the source data,
> but are being added during the insert/conversion process.)
>
> My questions are:
>
> 1. How do I determine what the character is?
> 2. How do I remove these characters from my columns.
>
> If I copy and paste the string from either Excel or the query results into
> Notepad, the added character appears to be removed. That is, no line feeds or
> tabs are detectable.
>
>