Removing returns and tabs 2

[developer155]

Hello,
I am trying to remove all characters from a field in db. I thought I removed them all but some fields still contain some characters (when the data is transferred to text file some fields have bunch of little square values. How do I find what character is in the field? Here is my code:




set @buffer = REPLACE(SUBSTRING( cast(@Field as varchar(256)), 1, 256), char(13)+char(10), ' ')
set @buffer = REPLACE(SUBSTRING( cast(@buffer as varchar(256)), 1, 256), char(10), ' ')
set @buffer = REPLACE(SUBSTRING( cast(@buffer as varchar(256)), 1, 256), char(13), ' ')


return @buffer


I thought I removed tabs, returns, spaces.. No?


thanks for any help!!!

 

[HARLEY12]

Tab is char(9) and spaces is char(32). You removed line feeds and carrige returns.

The table here may help:

http://www.asciitable.com/

You can also use the ASCII string function to find the others. ex:

PRINT ASCII('a') -- The ASCII value of the letter
"a" is 97

 

[ESquared]

create table #thetable (
   thefield varchar(50))
insert into #thetable
   select 'two' + char(13)+char(10) + 'lines' union
   select 'some other strange characters at the end ' + char(2) + char(11) + char(17) + char(27) + char(129) + char(144)

create table #t (
      num int,
      ch char(1)
   )
declare @c int
   set @c = 0
while @c<256 begin
   if char(@c) not like '[ 0-Z]'
      insert into #t
         select @c, Char(@c)
      set @c=@c+1
end
select
      Pos = charindex(ch,thefield),
      AsciiCode = num,
      Chr = SubString(thefield,charindex(ch,thefield),1),
      Value = thefield
   FROM #thetable
      cross join #t
   WHERE
      charindex(ch,thefield) > 0

drop table #t
drop table #thetable[/code]

This only finds the first instance of each particular nonprintable character, but should help you out nonetheless.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k