Help with query

Question

Post reply

Help with query

dva2007

SSCertifiable

Points: 7707
More actions
November 6, 2008 at 8:20 am

#195465

The following query is having different output. Why?
declare @a nchar(10)
declare @b-2 nchar(10)
declare @a1 char(10)
declare @b1 char(10)
set @a = '11111'
set @b-2 = '2'
set @a1 = '11111'
set @b1 = '2'
select replace(@a,'1',@b)
select replace(@a1,'1',@b1)
/**
2 2 2 2 2
22222
**/

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

nchar uses 2bytes ber character (unicode) ; char is not unicode and uses one byte per character.

* Noel

dva2007 SSCertifiable Points: 7707 More actions · Answer 2

Thanks for your prompt reply. It seems it uses more than 2 character. Because when i see the output it is like 10 character. Is it because i have declare nchar(10)?

dva2007 SSCertifiable Points: 7707 More actions · Answer 3

Then how about this?

declare @b1 nchar(10)

declare @b2 char(10)

set @b1 = 'B'

set @b2 = 'B'

select @b1 + 'a', @b2 + 'a'

--------------------

B a B a

--------------------

noeld SSC Guru Points: 96590 More actions · Answer 4

dva2007 (11/6/2008)
Then how about this?
declare @b1 nchar(10)
declare @b2 char(10)
set @b1 = 'B'
set @b2 = 'B'
select @b1 + 'a', @b2 + 'a'
--------------------
B a B a
--------------------

literals are "char" by default ... you can make then unicode by casting or using "N" prefix

Try this to see the difference:

declare @b1 nchar(10)

declare @b2 char(10)

set @b1 = 'B'

set @b2 = 'B'

select @b1 + 'a', @b2 + 'a'

select @b1 + cast('a' as nchar(10)), @b2 + cast('a' as nchar(10))

* Noel

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

The thing I'm struggling with on this one is that in my mind both CHAR and NCHAR are fixed width datatypes, so it seems to me that both should have the extra spaces after each 2 in the results of the queries. :unsure:

I tried the following:

select replace(@a,CAST('1' AS nchar),@b)

select replace(@a1,CAST('1' AS char),@b1)

and that gave me:

11111

22222

so there must be something in the REPLACE function that's treating CHAR and NCHAR differently, other than the 2 bytes per letter thing.