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...
I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.
(Borrowing JNK's table variable)
SELECT id,
Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1
Best Answer
If the "list_of_jobs" is a known set of values, you can use UNPIVOT to do this, see this document
If it is an unknown set of values, you can use STRING_SPLIT in SQL Server 2016 or higher, see this document. NOTE: Your database compatibility level needs to be 130 or higher.
If you have SQL Server 2014 or earlier, or your compatibility level is below 130, you will need to write a user function to do the string splitting. Here's a good example.