How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS?

Question

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.

Answer 1

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...

Answer 2

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