Replace special char (double space)

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

SQL Server 2005 Forums

Transact-SQL (2005)

Replace special char (double space)

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-03 : 06:23:36

Good day, I need help.

I found a way to replace the special character a single sapce in front of my value:
SELECT distinct replace(VALUE,char(13),'') as Sites

the value: " Test1" now returns "Test1" which is great
But i have some columns returning double space:
" Test2" will now return " Test2"

I tried putting in another select:
select distinct replace(Sites,char(13),'') as VSites FROM (
SELECT replace(VALUE,char(13),'') as Sites

But still returns: " Test2"

Regards

irfanshirur
Starting Member

21 Posts

Posted - 2009-11-03 : 06:28:37

quote:
Originally posted by ismailc

Good day, I need help.

I found a way to replace the special character a single sapce in front of my value:
SELECT distinct replace(VALUE,char(13),'') as Sites

the value: " Test1" now returns "Test1" which is great
But i have some columns returning double space:
" Test2" will now return " Test2"

I tried putting in another select:
select distinct replace(Sites,char(13),'') as VSites FROM (
SELECT replace(VALUE,char(13),'') as Sites

But still returns: " Test2"

Regards

For this you can ues the Ltrim(Rtrim(Column Name)) which will remove the space

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-03 : 06:52:38

Unfortunatley - i have tried that but no luck.

declare @str1 varchar(1000)
select @str1 = MAX(Control) from dbo.fcEventArchive where ID = 5691
select distinct replace(Sites,char(13),'') as Sites FROM (
SELECT replace(ltrim(rtrim(VALUE)),char(13),'') as Sites FROM (
SELECT SUBSTRING(@str1,charindex(']',@str1,v.number)+1,abs(charindex(']',@str1,charindex(']',@str1,v.number)+1)-charindex(']',@str1,v.number))) as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND substring(']' + @str1, v.number, 1) = ']')s WHERE [VALUE] <> ''
) v1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:13:56

replace char(160) by ''

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:17:54

quote:
Originally posted by madhivanan

replace char(160) by ''

Madhivanan

Failing to plan is Planning to fail

Hi madhi,

char(160)??
What leads you to assume it is 160?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:19:10

My thought was:
char(13) often comes together with char(10) --> CR/LF

No, you're never too old to Yak'n'Roll if you're too young to die.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-03 : 07:22:07

Hi, I feel i'm winning but not there

I tried 160 but no luck.
I then read & found that it is a Line Feed char(10)

replace(Sites,char(10),'') - this will return: No row
replace(Sites,char(10),'1') - this will return: : "1Test3"

I seem to have found the char but trying to remove it is difficult.

Please help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:26:22

Try this:
replace(replace(Sites,char(10),''),char(13),'')

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:26:40

Try

replace(replace(Sites,char(10),''),char(13),'')

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:27:59

Hey madhi - my twin

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:28:19

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

replace char(160) by ''

Madhivanan

Failing to plan is Planning to fail

Hi madhi,

char(160)??
What leads you to assume it is 160?

No, you're never too old to Yak'n'Roll if you're too young to die.

Sometimes, it may also cause some problem

select 'a'+char(160)+'a'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:30:03

quote:
Originally posted by webfred

Hey madhi - my twin

No, you're never too old to Yak'n'Roll if you're too young to die.

Thats popularlry knows as

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-03 : 07:31:00

Thank You, Thank You All

No words - i struggled since yesterday & thought i could fix it without posting:

replace(replace([VALUE],char(10),''),char(13),'')

Regards :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:31:09

You can call me sniper

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:31:47

quote:
Originally posted by ismailc

Thank You, Thank You All

No words - i struggled since yesterday & thought i could fix it without posting:

replace(replace([VALUE],char(10),''),char(13),'')

Regards :)

Fine! Welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:33:04

quote:
Originally posted by ismailc

Thank You, Thank You All

No words - i struggled since yesterday & thought i could fix it without posting:

replace(replace([VALUE],char(10),''),char(13),'')

Regards :)

So, this would also solve your SSRS problem (in other thread)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources