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?

回答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

回答2:

[Answer to the question in comment from 'Praveen']

As explained in the answer by DhruvJoshi, the collation is forced on both the sides.

To work around this problem, you could create a function (that performs the collation) which takes the 'string to be collated' as argument and returns collated string.

You could then call the function on one side of the check condition.

The function may look something like this:

create function dbo.convert_ascii(@input nvarchar(500))
returns varchar(500)
as
begin
declare @collatedstring varchar(500)
set @collatedstring = CAST(@input AS varchar(100)) 
COLLATE SQL_Latin1_General_CP1253_CI_AI
return collatedstring 
end

And you could invoke it this way:

if 'ÄÊÍABCDE' = dbo.convert_ascii('ÄÊÍABCDE')
print 'same' 
else 
print 'not same'

This should work.