TAb Key Removal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TAb Key Removal

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2011-04-04 : 11:38:21

Hi All,
I am not able to remove tab character from SQL Table.
I have tried

Replace(Fname,Char(9),'')

but it is replacing all the values as NULL.

I have tried this also
update qtemp/sometable
set somefield = replace(somefield, x'05', ' ')

but getting error becuse of single quotes in x'05.

Please help i am using sql 2000

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-04 : 12:27:18

try replacing char(10) and char(13) as well

set somefield = replace(somefield, x'05', ' ')
what are you trying to do here?
set somefield = replace(somefield, 'x05', ' ')?

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 12:35:02

Don't know if it is something special about SQL 2000, but on my SQL 2005, both of these work:

create table #tmp (data varchar(31));

insert into #tmp values ('x	x'); -- here i am typing in a tab in SSMS.
insert into #tmp values ('y'+char(9)+'y');

select data,len(data),cast(data as varbinary) from #tmp;

update #tmp set data = replace(data,'	','') where data like 'x%' -- here I am typing a tab in SSMS
update #tmp set data = replace(data,char(9),'') where data like 'y%'

select data,len(data),cast(data as varbinary) from #tmp;

drop table #tmp;

sanjay5219
Posting Yak Master

240 Posts

Posted - 2011-04-04 : 12:56:03

set somefield = replace(somefield, 'x05', ' ')?

i am trying to replace TAB from Hexadecimal. I got this code from one site where they shown that x'05' is a hexadecimal value of tab key

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-04 : 13:12:33

SQL Server doesn't use hexadecimal representations for character codes. It also uses the ASCII character set, and 0x05 does not translate to the ASCII code for Tab. If you want to replace that character:

REPLACE(somefield, CHAR(5), '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources