sql-servert-sqlsql-server-2012

Convert to ASCII char in SQL Server

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?

Solution

  • 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