I have the following string
我有下面的字符串。
áéíóú
which I need to convert it to
我需要把它转换成?
aeiou
How can I achieve it? (I don't need to compare, I need the new string to save)
我怎样才能做到呢?(我不需要比较,我需要新的字符串来保存)
3 个解决方案
#1
85
Try using COLLATE
:
试着用整理:
select 'áéíóú' collate SQL_Latin1_General_Cp1251_CS_AS
For Unicode data, try the following:
关于Unicode数据,请尝试以下操作:
select cast(N'áéíóú' as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS
I am not sure what you may lose in the translation when using the second approach.
我不确定在使用第二种方法时,您在翻译过程中会失去什么。
Update
更新
It looks like œ
is a special case, and we have to handle upper and lower case separately. You can do it like this (this code is a good candidate for a user-defined function):
œ似乎是一个特例,我们必须处理分别大写和小写。您可以这样做(此代码是用户定义函数的一个很好的候选):
declare @str nvarchar(max) = N'ñaàeéêèioô; Œuf un œuf'
select cast(
replace((
replace(@str collate Latin1_General_CS_AS, 'Œ' collate Latin1_General_CS_AS, 'OE' collate Latin1_General_CS_AS)
) collate Latin1_General_CS_AS, 'œ' collate Latin1_General_CS_AS, 'oe' collate Latin1_General_CS_AS) as varchar(max)
) collate SQL_Latin1_General_Cp1251_CS_AS
-- Output:
-- naaeeeeioo; Oeuf un oeuf
#2
2
I had the same problem. In Greek for proper conversion to UPPER() you must suppress accent. Changing collation caused issues in other applications. Putting some REPLACE() functions I had more control on the behavior maintaining collation. Below is my ToUpperCaseGR function.
我也有同样的问题。在希腊语中,要正确地转换为UPPER(),你必须抑制重音。更改排序规则会在其他应用程序中引起问题。放置一些REPLACE()函数可以更好地控制行为维护排序。下面是ToUpperCaseGR函数。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION ToUpperCaseGR
(
@word nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
-- Declare the return variable here
declare @res nvarchar(max)
set @res = UPPER(@word)
set @res = replace(@res,'Ά','Α')
set @res = replace(@res,'Έ','Ε')
set @res = replace(@res,'Ί','Ι')
set @res = replace(@res,'Ή','Η')
set @res = replace(@res,'Ό','Ο')
set @res = replace(@res,'Ύ','Υ')
set @res = replace(@res,'Ώ','Ω')
-- Return the result of the function
RETURN @res
END
GO
#3
0
Use this function:
使用这个函数:
CREATE FUNCTION [dbo].[F_RemoveDiacritics] (
@String varchar(max)
) RETURNS varchar(max)
AS BEGIN
DECLARE @StringResult VARCHAR(max);
select @StringResult= @String collate SQL_Latin1_General_Cp1251_CS_AS
return @StringResult
END
#1
85
Try using COLLATE
:
试着用整理:
select 'áéíóú' collate SQL_Latin1_General_Cp1251_CS_AS
For Unicode data, try the following:
关于Unicode数据,请尝试以下操作:
select cast(N'áéíóú' as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS
I am not sure what you may lose in the translation when using the second approach.
我不确定在使用第二种方法时,您在翻译过程中会失去什么。
Update
更新
It looks like œ
is a special case, and we have to handle upper and lower case separately. You can do it like this (this code is a good candidate for a user-defined function):
œ似乎是一个特例,我们必须处理分别大写和小写。您可以这样做(此代码是用户定义函数的一个很好的候选):
declare @str nvarchar(max) = N'ñaàeéêèioô; Œuf un œuf'
select cast(
replace((
replace(@str collate Latin1_General_CS_AS, 'Œ' collate Latin1_General_CS_AS, 'OE' collate Latin1_General_CS_AS)
) collate Latin1_General_CS_AS, 'œ' collate Latin1_General_CS_AS, 'oe' collate Latin1_General_CS_AS) as varchar(max)
) collate SQL_Latin1_General_Cp1251_CS_AS
-- Output:
-- naaeeeeioo; Oeuf un oeuf
#2
2
I had the same problem. In Greek for proper conversion to UPPER() you must suppress accent. Changing collation caused issues in other applications. Putting some REPLACE() functions I had more control on the behavior maintaining collation. Below is my ToUpperCaseGR function.
我也有同样的问题。在希腊语中,要正确地转换为UPPER(),你必须抑制重音。更改排序规则会在其他应用程序中引起问题。放置一些REPLACE()函数可以更好地控制行为维护排序。下面是ToUpperCaseGR函数。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION ToUpperCaseGR
(
@word nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
-- Declare the return variable here
declare @res nvarchar(max)
set @res = UPPER(@word)
set @res = replace(@res,'Ά','Α')
set @res = replace(@res,'Έ','Ε')
set @res = replace(@res,'Ί','Ι')
set @res = replace(@res,'Ή','Η')
set @res = replace(@res,'Ό','Ο')
set @res = replace(@res,'Ύ','Υ')
set @res = replace(@res,'Ώ','Ω')
-- Return the result of the function
RETURN @res
END
GO
#3
0
Use this function:
使用这个函数:
CREATE FUNCTION [dbo].[F_RemoveDiacritics] (
@String varchar(max)
) RETURNS varchar(max)
AS BEGIN
DECLARE @StringResult VARCHAR(max);
select @StringResult= @String collate SQL_Latin1_General_Cp1251_CS_AS
return @StringResult
END