Convert to ASCII char in sql server

Question

In Oracle I can convert ÄÊÍABCDE to AEIABCDE using:

SELECT CONVERT('ÄÊÍABCDE', 'US7ASCII') from dual;

Output:

AEIABCDE

How can I do the same in SQL Server?

Answer 1

you can try following query:

 SELECT CAST('ÄÊÍABCDE' AS varchar(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI

Caveat:

This does not support UNICODE strings so do not replace varchar with nvarchar

demo sql fiddle: http://sqlfiddle.com/#!6/9eecb7/2016

Explanation:

Read about collate on MSDN here: https://msdn.microsoft.com/en-us/library/ms184391.aspx

Edit:

On comment

if 'ÄÊÍABCDE' = CAST('ÄÊÍABCDE' AS varchar(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI print 'same' else print 'not same' prints same. Why??

Collation is forced in the WHERE condition which collates both side of comparison. If you need not same as result, try below

declare @t varchar
set @t= CAST('ÄÊÍABCDE' AS varchar(100)) 
COLLATE SQL_Latin1_General_CP1253_CI_AI
select 
case 
when 'ÄÊÍABCDE' like  @t
then 'same' else  'not same' end as result

demo link: http://sqlfiddle.com/#!6/9eecb7/2022