Sometimes, you want to convert Vietnamese name with Unicode font to English name without accent. How can you do that in T-SQL?
Here's my function to do that:
CREATE FUNCTION dbo.fnConvertToEnglishName(@VNString NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @ENString NVARCHAR(4000)
DECLARE @I INT, @J INT
DECLARE @Array NVARCHAR(1000)
SET @Array = N'àảãáạằẳẵắặăầẩẫấậâa' +
N'èẻẽéẹềểễếệêeeeeeee' +
N'ìỉĩíịiiiiiiiiiiiii' +
N'òỏõóọồổỗốộôờởỡớợơo' +
N'ùủũúụừửữứựưuuuuuuu' +
N'ÈẺẼÉẸỀỂỄẾỆÊEEEEEEE' +
N'ÌỈĨÍỊIIIIIIIIIIIII' +
N'ÒỎÕÓỌỒỔỖỐỘÔỜỞỠỚỢƠO' +
N'ÀẢÃÁẠẰẲẴẮẶĂẦẨẪẤẬÂA' +
N'ÙỦŨÚỤỪỬỮỨỰƯUUUUUUU' +
N'ỳỷỹýỵyyyyyyyyyyyyy' +
N'ỲỶỸÝỴYYYYYYYYYYYYY' +
N'đddddddddddddddddd' +
N'ĐDDDDDDDDDDDDDDDDD'
SET @ENString = @VNString;
SET @I = 1;
SET @J = 1;
WHILE @I <= LEN(@Array) BEGIN IF (@I%18 = 0) BEGIN SET @J = @J + 1 END ELSE BEGIN IF CAST(SUBSTRING(@Array, @I, 1) AS VARBINARY) <> CAST(SUBSTRING(@Array, 18*@J, 1) AS VARBINARY)
SET @ENString = Replace(@ENString COLLATE Latin1_General_BIN,
SUBSTRING(@Array, @I, 1) ,
SUBSTRING(@Array, 18*@J, 1) );
END
SET @I = @I + 1;
END
RETURN @ENString;
END
Tuesday, August 28, 2007
How to convert Vietnamese name to English name?
at 10:33 PM
Labels: SQL Server, SQL Server 2000, SQL Server 2005, T-SQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment