Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I create a table like this

create table char_test(
    item varchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go

I can store varchars containing

insert into char_test values ('≥');

When I do a

select item from  char_test;

I get

item
----------
=

But using

select replace(item, '≥', '>=') from  char_test;

gives

-----------------
>=

So the database knows the character. How can I convert the string to a nvarchar showing this character?

Edit:

The important fact here is, that SQL Server here maps some different characters to the same character without throwing errors or warnings.

I dare call this very bad design.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

varchar doesn't support : unicode only

This includes the literal '≥' which is varchar too

Collation here doesn't matter: this is sorting and comparison only never seen this before!

Some more SQL to use your table

SELECT ASCII('≥'), CHAR(61), '≥'
GO
insert into char_test values ('≥');
GO
select item, ASCII(item) from  char_test;
GO
select replace(item, '≥', '>='), replace(item, '=', '>=') from  char_test;
GO


create table nchar_test(
    item varchar(10) collate SQL_Latin1_General_CP1_CI_AS,
    Nitem nvarchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
insert into nchar_test values ('≥', N'≥');
GO
select item, ASCII(item), Nitem, UNICODE(Nitem) from nchar_test;
GO

Edit: after more playing

If you have a value then use CP 437

select CAST(Nitem COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar) from nchar_test;
GO
select CAST(N'≥' COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar)
GO

Note: the ASCII is 242 but this won't give either :-)

SELECT
   ASCII(CAST(N'≥' COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar)),
   CHAR(242),
   CHAR(242) COLLATE SQL_Latin1_General_CP437_CI_AS

You may be able to control this with database collation but frankly use unicode...

share|improve this answer
    
ah....it was the N before the '≥' I was missing, good catch. –  SQLRockstar Apr 13 '11 at 14:35
add comment

If I run this:

select UNICODE ( '≥' )
select UNICODE ( '=' )
select UNICODE ( item ) from  char_test;

They all return the same number, 61. I don't think the REPLACE proves that the character is being recognized. I even changed the char_test table to have the item column defined as nvarchar(10) and it still doesn't recognize the '≥'.

My best guess here is that you need a different collation, or to insert the data using a different method than SSMS (assuming you did the same as I did).

HTH

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.