Tuesday, August 28, 2007

How to convert Vietnamese name to English name?

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

No comments:

Post a Comment