Bảng Danh Sách Tên Họ Theo Thứ Tự ABC
Một trong những điều "khó chịu" nhất của CSDL tại Việt Nam nói riêng, và tại những quốc gia dùng mẫu tự La-tinh có bỏ dấu nói chung, là phải tìm cách sắp xếp bảng (Table) theo thứ tự ABC...
Hoa Kỳ là quốc gia đầu tiên sáng lập lên bảng mẫu tự ASCII, và cũng là một quốc gia tiền phong sáng lập ra những CSDL như ORACLES, MS-SQL, SyBase, cho nên việc sắp xếp mẫu tự theo thứ tự cho những mẫu tự có bỏ dấu không được họ nghiên cứu tới. Do đó, khi viết một lập trình CSDL cho tiếng Việt, chúng ta gặp nhiều khó khăn trong việc trình bày một bảng dữ liệu theo thứ tự ABC.
Việc trình bày một bảng DL theo thứ tự ABC với Anh-ngữ thật dễ dàng như sau:
SELECT first_name, last_name FROM PERSON (NOLOCK)
ORDER BY first_name, last_name
Bởi vì tiếng Anh không có dấu, cho nên lệnh từ SELECT ở trên sẽ cho chúng ta một bảng dữ liệu tên họ theo thứ tự. Nhưng nếu hồ sơ PERSON chứa đựng tên họ của người Việt như Hồng Nguyễn, Hải Trần, thì thứ tự ABC sẽ bị đảo lộn vô trật tự, rất khó coi. Tôi đã gặp nhiều lập trình tiếng Việt vấp phải vấn đề này, và đây là một vấn đề không dễ giải quyết.
Trong bài ra mắt (đầu tiên) trên diễn đàn này, tôi mạo muội trình bày một phương án có thể giải đáp được vấn đề thứ tự ABC cho một CSDL tiếng Việt. Trước khi đề ra phương án,tôi cũng xin được thẳng thắn nói trước rằng, trong lãnh vực khoa học, không ai có thể tự hào rằng phương án của mình là phương án hay nhất, tốt nhất. Người Mỹ có câu tục ngữ đại ý rằng "luôn luôn có nhiều cách để giải quyết một vấn đề." Do đó, nếu chỉ một, hai người trong bạn có thể dùng phương án của tôi vào lập trình của các bạn, tôi lấy điều đó làm vui. Ngược lại, nếu có bạn nào có phương án hay hơn, hoặc thấy phương án của tôi có những khuyết điểm, cũng xin vui lòng chỉ điểm, để chúng ta cùng học hỏi.
Xin trở lại vấn đề chính. Nếu các bạn có một hồ sơ (table) như sau:
CREATE TABLE dbo.PERSON
(
ten NVARCHAR(20) NOT NULL,
ten_lot NVARCHAR(10) NULL,
ten_ho NVARCHAR(20) NOT NULL,
CONSTRAINT [PK_PERSON] PRIMARY KEY NONCLUSTERED (ten,ten_lot,ten_ho)
)
Chắc chắn các bạn sẽ không thể trình bày một danh sách theo thứ tự ABC. Lý do rất giản dị vì lệnh từ ORDER BY sắp xếp thứ tự theo mã số của Unicode. Vậy thì, câu hỏi là làm sao chúng ta có thể có một bảng danh sách theo thứ tự ABC của tiếng Việt? Hơn thế nữa, khi viết một lập trình CSDL, tốc độ xử lý cũng không thể không nghĩ tới. Câu hỏi trở nên hóc búa hơn: Làm sao để có thể trình bày một bảng danh sách họ tên theo thứ tự ABC với tốc độ xử lý nhanh chóng nhất?
Phương án giải quyết của tôi như sau:
1. Chúng ta cấu tạo lại hồ sơ (table) PERSON như sau:
CREATE TABLE dbo.PERSON
(
tenho_abc NVARCHAR(60) NOT NULL,
ten NVARCHAR(20) NOT NULL,
ten_lot NVARCHAR(10) NULL,
ten_ho NVARCHAR(20) NOT NULL,
CONSTRAINT [PK_PERSON] PRIMARY KEY NONCLUSTERED (tenho_abc)
)
Như đã thấy ở trên, tôi cấu tạo thêm một danh mục (column) nữa với tên là tenho_abc. Tôi cũng thay đổi khóa chính (primary key) của hồ sơ để tốc độ xử lý có thể tốt hơn. Dĩ nhiên, nếu đây là một hồ sơ tại nơi làm việc, tôi cũng sẽ đặt thêm những khóa phụ (secondary key) cho ten và ten_ho để cho việc tìm kiếm (look-up) cá nhân được mau chóng hơn.
2. Tạo một function để mỗi khi tên, ten_lot, và ten_ho thay đổi, thì tenho_abc cũng thay đổi theo. Chúng ta có thể dùng TRIGGER vào việc này. Nhưng TRIGGER cũng là một con dao hai lưỡi - vào một dịp khác tôi sẽ trình bày tại sao trigger là một con dao hai lưỡi. Cho nên, tôi sẽ tránh không dùng trigger. function dùng để xử lý tenho_abc như sau:
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[fnc_get_sort_string]') and xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[fnc_get_sort_string]
GO
CREATE FUNCTION [dbo].[fnc_get_sort_string]
(
@szLastName NVARCHAR(MAX),
@szMidName NVARCHAR(MAX),
@szFirstName NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
--WITH ENCRYPTION
AS
BEGIN
DECLARE @idx INT
DECLARE @szLen INT
DECLARE @iFound INT
DECLARE @iRemain INT
DECLARE @szVows NVARCHAR(MAX)
DECLARE @szRetString NVARCHAR(MAX)
DECLARE @szName NVARCHAR(MAX)
DECLARE @szChar NCHAR
SET @szRetString = N''
SET @szName = Lower(LTRIM(@szLastName) + LTRIM(@szMidName) + LTRIM(@szFirstName))
SET @szVows = N'aáàảãạăắằẳẵặâấầẩẫậe éèẻẽẹêếềểễệiíìỉĩịoóò õọơớờởỡợôốồổỗộuúùủũ ụưứừửữựýỳỷỹỵdđ'
SET @idx = 0
SET @szLen = LEN(@szName)
WHILE(@idx < @szLen)
BEGIN
SET @idx = @idx + 1
SET @szChar = SUBSTRING(@szName,@idx,1)
SET @iFound = CHARINDEX(@szChar,@szVows)
IF (@iFound > 0)
BEGIN
SET @iRemain = @iFound % 6
IF(0 < @iRemain)
BEGIN
SET @iFound = (@iFound / 6) + 1
END
ELSE
BEGIN
SET @iFound = @iFound / 6
SET @iRemain = 6
END
IF(@iFound = 1)
BEGIN
SET @szRetString = @szRetString + N'a'
SET @szRetString = @szRetString + NCHAR(65 + @iRemain)
END
ELSE IF (@iFound = 2)
BEGIN
SET @szRetString = @szRetString + N'a'
SET @szRetString = @szRetString + NCHAR(65 + @iRemain + 10)
END
ELSE IF (@iFound = 3)
BEGIN
SET @szRetString = @szRetString + N'a'
SET @szRetString = @szRetString + NCHAR(65 + @iRemain + 20)
END
ELSE IF (@iFound = 4)
BEGIN
SET @szRetString = @szRetString + N'e'
SET @szRetString = @szRetString + NCHAR(65 + @iRemain)
END
ELSE IF (@iFound = 5)
BEGIN
SET @szRetString = @szRetString + N'e'
SET @szRetString = @szRetString + NCHAR(65 + @iRemain + 10)
END
ELSE IF (@iFound = 6)
BEGIN
SET @szRetString = @szRetString + N'i'
SET @szRetString = @szRetString + NCHAR(65 + @iRemain)
END
ELSE IF (@iFound = 7)
BEGIN
SET @szRetString = @szRetString + N'o' + NCHAR(65 + @iRemain)
END
ELSE IF (@iFound = 8)
BEGIN
SET @szRetString = @szRetString + N'o' + NCHAR(65 + @iRemain + 10)
END
ELSE IF (@iFound = 9)
BEGIN
SET @szRetString = @szRetString + N'o' + NCHAR(65 + @iRemain + 20)
END
ELSE IF (@iFound = 10)
BEGIN
SET @szRetString = @szRetString + N'u' + NCHAR(65 + @iRemain)
END
ELSE IF (@iFound = 11)
BEGIN
SET @szRetString = @szRetString + N'u' + NCHAR(65 + @iRemain + 10)
END
ELSE IF (@iFound = 12)
BEGIN
SET @szRetString = @szRetString + N'y' + NCHAR(65 + @iRemain)
END
ELSE IF (@iFound = 13)
BEGIN
SET @szRetString = @szRetString + N'd' + NCHAR(65 + @iRemain)
END
END
ELSE
BEGIN
SET @szRetString = @szRetString + @szChar
END
END
RETURN @szRetString
END
*** Dẫn giải
a) Lệnh từ SQL đầu tiên IF EXISTS(...) dùng để cập nhật hóa function. Xin nhớ ký, nếu bạn là một lập trình gia thực thụ dùng máu xám để nuôi gia đình, thì chắc chắn các bạn cần biết, khi viết một function, hay stored procedure, các bạn phải có phương án để cập nhật chúng. Lệnh từ đầu tiên này dùng để xóa bỏ function isp_get_sort_string nếu nó đang tồn tại trong CSDL. Nếu không có lệnh từ này, thì lệnh từ kế tiếp sẽ bị lỗi nếu function isp_get_sort_string đã có sẵn trong CSDL.
b) lệnh từ CREATE FUNCTION (...) dùng để cấu tạo function isp_get_sort_string. Các bạn có thể thay đổi tên của nó, chẳng hạn như isp_tạo_tenho_abc(). Các bạn cũng có thể thay đổi tên của những biến-từ (variable), như @szFirstName thành @ten, v.v... Điều quan trọng ở đây là không bao giờ đặt tên của function hay stored procudure bắt đầu bằng hai mẫu tự SP..., vì nó sẽ làm giảm tốc độ xử lý. Lý do là mỗi khi MS-SQL xử lý một stored procedure có tên bắt đầu bằng SP, MS-SQL sẽ tìm cái procedure đó ở CSDL master trước.
Trong một bài khác, tôi sẽ giải tường tận từng hàng một cho function isp_get_sort_string. Nhưng trong phạm vi của bài này, tôi chỉ xin tóm tắt như sau: function isp_get_sort_string() nhận tên,tên lót, và tên họ, và trả lại một biến từ có cả chữ gom lại cộng thêm những mẫu tự sắp xếp để chúng ta có thể có một bảng danh sách tên và họ theo thứ tự ABC. Tôi cũng khuyến khích các bạn tự tìm hiểu bằng cách nghiên cứu những lệnh từ trong function này, và gửi cho tôi những câu hỏi. Nếu số người hỏi nhiều,tôi sẽ có một bài viết khác để trả lời chung thật sớm.. Nếu không có ai hỏi, tôi sẽ tự biết rằng tôi không có nhiều độc giả, và dĩ nhiên, bài viết kế tiếp của tôi sẽ không ra đời sớm sủa (theo đúng luật cung cầu).
Tóm tắt lại, function isp_get_sort_string() sẽ cho chúng ta một biến-từ để chúng ta có thể giữ trong danh mục tenho_abc.
3. INSERT và UPDATE
Khi bỏ tên tuổi vào hồ sơ (INSERT), hoặc khi sửa chữa tên tuổi (UPDATE), chúng ta cần phải xử lý danh mục tenho_abc, để danh mục này có những dữ kiện phù hợp với ten, ten_lot, và ten_ho.
a) INSERT
mỗi khi cho thêm một danh mục vào hồ sơ, các bạn cần phải xử lý tenho_abc, thí dụ như sau:
INSERT dbo.PERSON (tenho_abc, ten, ten_lot,ten_ho)
VALUES(EXEC dbo.isp_get_sort_string('Hồng','Đào','Nguyễn ') ,'Hồng','Đào','Nguyễn')
b) UPDATE
UPDATE dbo.PERSON
SET tenho_abc = EXEC dbo.isp_get_sort_string('Hồng','Đào','Trần')
ten = 'Hồng',
ten_lot = 'Đào',
tên_ho = 'Trần'
WHERE tenho_abc = (EXEC dbo.isp_get_sort_string('Hồng','Đào','Nguyễn '))
4. Lấy danh sách theo mẫu tự:
Những việc khó khăn đã làm xong. Bây giờ, để có một danh sách tên họ theo thứ tự ABC sẽ trở lên dễ dàng với nhiều thích thú. Các bạn chỉ dùng lệnh từ SELECT như sau:
SELECT ten,ten_lot, ten_ho FROM PERSON (NOLOCK)
ORDER BY tenho_abc
SQL sẽ cho các bạn một danh sách theo thứ tự ABC của tên, tên đệm, và họ. Áp dụng phương án này vào lập trình ABC, các bạn chắc chắn sẽ làm cho khách hàng vừa lòng.
Chúc tất cả.
DQ
Tác-quyền(CopyRight): Bài viết và những phương án lập trình trong bài viết này có thể dùng bất cứ ở đâu với bất cứ mục đích gì. Nhưng tên của tác giả phải được chú thích mỗi khi dùng.
