การค้นหาข้อความแบบ LIKE %% คือการค้นหาส่วนใดส่วนหนึ่งของฟิลด์นั้นๆ
แต่ถ้าข้อความที่พิมพ์ค้นหากรอกเพี้ยนไปสักตัวนึงล่ะ การค้นหาก็จะไม่แสดงผลลัพธ์ให้เห็น
สังเกตภาพด้านล่างนี้ ค้นหาด้วยคำว่า "จักพงษ์" แต่คำว่า จัก ขาด ร.เรือ ไปหนึ่งตัว
มาลองดูข้อมูลจากฟังก์ชั่น LEVENSHTEIN() ว่าจะนำไปใช้ยังไงกันดี
ในรูป จะเห็นว่ามีผลต่างของคำค้นหากับเรคอร์ดที่ 3 อยู่ 1 ตัวอักษร เพราะตกอักษร ร.เรือ นั่นเอง
ลองค้นหาด้วยผลต่างที่ 1 ตัวอักษร เราก็จะได้เรคอร์ดมาแสดง 1 รายการ
หรือถ้าต้องการรายการที่ใกล้เคียงเรคอร์ดอื่นๆด้วย ก็เปลี่ยนเป็น 0 AND 2 ก็จะได้รายการอื่นๆที่มีผลต่างแค่ 2 ตัวอักษรมาด้วย ซึ่งถ้าต้องการที่ใกล้เคียงมากกว่านี้ก็เพิ่มตัวเลขเข้าไปเรื่อยๆครับ
มาดูส่วนของการสร้างคำสั่ง SQL เพื่อสร้างฟังก์ชั่นไว้ใช้งานกันครับ
เริ่มจากการสร้างฟังก์ชั่น LEVENSHTEIN
Levenshtein distance Function
จากนั้น เมื่อต้องการนำฟังก์ชั่น LEVENSHTEIN() ไปใช้งานก็สามารถนำไปใช้ได้เลยดังตัวอย่างนี้
ข้อสังเกตมีอยู่ว่าถ้าเราใช้แค่
WHERE
LEVENSHTEIN(firstname, @string_search) BETWEEN 0 AND 2
ข้อมูลจะต้องตรงกันตั้งแต่ตัวเริ่มต้น เพราะถ้าเกิดคำที่ค้นหาอยู่ด้านท้ายประโยค ก็จะค้นหาไม่เจอ
ลองเขียน SELECT ฟิลด์เพิ่มอีกค่า เป็นการตรวจสอบจำนวนตัวอักษรที่เหมือนกัน
CHAR_LENGTH(firstname) - LEVENSHTEIN(firstname, @string_search) AS match_search
จะเห็นว่ามีอยู่ 3 รายการที่มีตัวอักษรตรงกับคำว่า "พงษ์"
และเมื่อเพิ่ม OR เพื่อเลือกรายการอื่นๆ ที่ตรงกับคำค้นหาเพิ่มเติม ก็จะได้ทั้งหมดเป็น 3 รายการ แต่สังเกตว่าบางรายการที่ข้อความยาวๆ อาจจะมีคำบางคำที่ตรงกันมาด้วย
วิธีที่แอดมินใช้ก็คือ จัดเรียงใหม่ ให้ผลลัพธ์ที่ใกล้เคียงกว่า แสดงก่อน
ก็จะได้รายการที่ต้องการที่แสดงอันดับแรกๆ
อ้างอิง
How to find similar results and sort by similarity?
https://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity
Levenshtein distance in mySQL
https://lucidar.me/en/web-dev/levenshtein-distance-in-mysql
MySQL : strange LENGTH() behaviour on utf8 string
https://stackoverflow.com/questions/16278898/mysql-strange-length-behaviour-on-utf8-string
แต่ถ้าข้อความที่พิมพ์ค้นหากรอกเพี้ยนไปสักตัวนึงล่ะ การค้นหาก็จะไม่แสดงผลลัพธ์ให้เห็น
สังเกตภาพด้านล่างนี้ ค้นหาด้วยคำว่า "จักพงษ์" แต่คำว่า จัก ขาด ร.เรือ ไปหนึ่งตัว
มาลองดูข้อมูลจากฟังก์ชั่น LEVENSHTEIN() ว่าจะนำไปใช้ยังไงกันดี
ในรูป จะเห็นว่ามีผลต่างของคำค้นหากับเรคอร์ดที่ 3 อยู่ 1 ตัวอักษร เพราะตกอักษร ร.เรือ นั่นเอง
ลองค้นหาด้วยผลต่างที่ 1 ตัวอักษร เราก็จะได้เรคอร์ดมาแสดง 1 รายการ
หรือถ้าต้องการรายการที่ใกล้เคียงเรคอร์ดอื่นๆด้วย ก็เปลี่ยนเป็น 0 AND 2 ก็จะได้รายการอื่นๆที่มีผลต่างแค่ 2 ตัวอักษรมาด้วย ซึ่งถ้าต้องการที่ใกล้เคียงมากกว่านี้ก็เพิ่มตัวเลขเข้าไปเรื่อยๆครับ
มาดูส่วนของการสร้างคำสั่ง SQL เพื่อสร้างฟังก์ชั่นไว้ใช้งานกันครับ
เริ่มจากการสร้างฟังก์ชั่น LEVENSHTEIN
Levenshtein distance Function
DELIMITER $$
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR;
-- max strlen=255
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
IF s1 = s2 THEN
RETURN 0;
ELSEIF s1_len = 0 THEN
RETURN s2_len;
ELSEIF s2_len = 0 THEN
RETURN s1_len;
ELSE
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
END WHILE;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
WHILE j <= s2_len DO
SET c = c + 1;
IF s1_char = SUBSTRING(s2, j, 1) THEN
SET cost = 0; ELSE SET cost = 1;
END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
IF c > c_temp THEN SET c = c_temp; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
IF c > c_temp THEN
SET c = c_temp;
END IF;
SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
END WHILE;
SET cv1 = cv0, i = i + 1;
END WHILE;
END IF;
RETURN c;
END$$
DELIMITER ;
จากนั้น เมื่อต้องการนำฟังก์ชั่น LEVENSHTEIN() ไปใช้งานก็สามารถนำไปใช้ได้เลยดังตัวอย่างนี้
SET @string_search = 'พงษ์';
SELECT
CONCAT(@string_search,' =>') AS 'คำค้นหา =>',
firstname ,
CHAR_LENGTH(firstname) AS 'จำนวนข้อความใน Field',
CHAR_LENGTH(@string_search) AS 'ความยาวของคำค้นหา',
LEVENSHTEIN(firstname, @string_search) AS 'จำนวนที่ไม่ตรง',
CHAR_LENGTH(firstname) - LEVENSHTEIN(firstname, @string_search) AS match_search
FROM
tb_student
WHERE
LEVENSHTEIN(firstname, @string_search) BETWEEN 0 AND 2
OR
CHAR_LENGTH(firstname) - LEVENSHTEIN(firstname, @string_search) = CHAR_LENGTH(@string_search)
ORDER BY
LEVENSHTEIN(firstname, @string_search) ASC
ข้อสังเกตมีอยู่ว่าถ้าเราใช้แค่
WHERE
LEVENSHTEIN(firstname, @string_search) BETWEEN 0 AND 2
ข้อมูลจะต้องตรงกันตั้งแต่ตัวเริ่มต้น เพราะถ้าเกิดคำที่ค้นหาอยู่ด้านท้ายประโยค ก็จะค้นหาไม่เจอ
ลองเขียน SELECT ฟิลด์เพิ่มอีกค่า เป็นการตรวจสอบจำนวนตัวอักษรที่เหมือนกัน
CHAR_LENGTH(firstname) - LEVENSHTEIN(firstname, @string_search) AS match_search
จะเห็นว่ามีอยู่ 3 รายการที่มีตัวอักษรตรงกับคำว่า "พงษ์"
และเมื่อเพิ่ม OR เพื่อเลือกรายการอื่นๆ ที่ตรงกับคำค้นหาเพิ่มเติม ก็จะได้ทั้งหมดเป็น 3 รายการ แต่สังเกตว่าบางรายการที่ข้อความยาวๆ อาจจะมีคำบางคำที่ตรงกันมาด้วย
วิธีที่แอดมินใช้ก็คือ จัดเรียงใหม่ ให้ผลลัพธ์ที่ใกล้เคียงกว่า แสดงก่อน
ก็จะได้รายการที่ต้องการที่แสดงอันดับแรกๆ
อ้างอิง
How to find similar results and sort by similarity?
https://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity
Levenshtein distance in mySQL
https://lucidar.me/en/web-dev/levenshtein-distance-in-mysql
MySQL : strange LENGTH() behaviour on utf8 string
https://stackoverflow.com/questions/16278898/mysql-strange-length-behaviour-on-utf8-string
PHP CI MANIA - PHP Code Generator
โปรแกรมช่วยสร้างโค้ด "ลดเวลาการเขียนโปรแกรม"
ความคิดเห็น
แสดงความคิดเห็น