Query help

Question

Post reply

Query help

karthik M

One Orange Chip

Points: 29139
More actions
April 10, 2008 at 8:00 am

#69913

Dear All,
declare @a varchar(30)
select @a = "a b"
select @a = replace(@a, char(32), rtrim(''))
select @a
output:
ab
can onyone explain me the usage of char(32) ?
How it removes the space from the middle of string ?
Inputs are welcome!
karthik

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Char(32) is the ISO code for a space

http://htmlhelp.com/reference/charset/iso032-063.html

the replace function searches for all instances of space and replaces them with rtrim("") (nothing)

GSquared SSC Guru Points: 260824 More actions · Answer 2

The char() function (not to be confused with the char() datatype) returns whatever character is assigned to the number in the parentheses.

Examples:

select char(33), char(34), char(35)

returns:

!"#

char(32) happens to be the space character.

Since replace gets rid of the second parameter and replaces it with the third, in the first, it gets rid of the spaces (char(32)), and replaces them with rtrim('') (zero-length string).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

karthik M One Orange Chip Points: 29139 More actions · Answer 3

karthik M

One Orange Chip

Points: 29139

April 10, 2008 at 8:19 am

#801001

Thanks SteveB and Gsquared.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Gsquared and SteveB,

Suppose if i want to replace some special characters from a string, shall i use char() function ?

Say for example,

Declare @STR varchar(15)

set @STR = '13-08-2008'

select replace(@str,xx,char(47))

here xxx can contain any character apart from /. say for example 13.08.2008 or 13,08,2008.

how to implement the above scenarion ?

karthik

GSquared SSC Guru Points: 260824 More actions · Answer 5

You can either use the char() function, or you can use the characters you want directly.

For example:

replace('abc', 'b', '1')

Will result in the "b" being replaced by a "1", which would give "a1c".

The char() version of this would be:

replace('abc', char(98), char(49))

You can also mix them:

replace('abc', 'b', char(49))

Anything works, so long as SQL can figure out what characters you want. You can use column names from a table, you can use variables, you can use char(), you can use "string literals" (e.g.: 'b', '1'), etc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon