วิธีใช้ฟังก์ชัน SQL REPLACE()

สรุปสั้นๆ

• REPLACE(string, old_substring, new_substring) แทนที่ทุกการปรากฏของสตริงย่อยได้ภายในครั้งเดียว

• เชนคำสั่งเพื่อแทนที่หลายสตริงย่อย: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• ส่งสตริงว่างเพื่อเอาอักขระออก: REPLACE(phone, '-', '')

• หากอาร์กิวเมนต์ใดเป็น NULL ฟังก์ชันจะคืนค่า NULL สำหรับแถวนั้น

• ความไวต่ออักษรพิมพ์เล็ก-ใหญ่ขึ้นอยู่กับฐานข้อมูลและ collation—SQL Server และ MySQL โดยค่าเริ่มต้นไม่แยกแยะตัวพิมพ์; PostgreSQL โดยค่าเริ่มต้นแยกแยะตัวพิมพ์

ทำความเข้าใจกับฟังก์ชัน SQL REPLACE()

ฟังก์ชัน REPLACE() ใน SQL จะค้นหาสตริงย่อยหรือสตริงที่ระบุในคอลัมน์และแทนที่ด้วยสตริงที่กำหนดอีกค่าหนึ่ง ตัวอย่างด้านล่างแสดงการใช้งานพื้นฐานของฟังก์ชัน REPLACE() อาร์กิวเมนต์ของฟังก์ชันประกอบด้วย:

• string: สตริงต้นฉบับที่ต้องการทำการแทนที่

• old_substring: สตริงย่อยที่ต้องการแทนที่

• new_substring: สตริงย่อยที่จะใช้แทนของเดิม

REPLACE(string, old_substring, new_substring)

เมื่อใช้ฟังก์ชัน REPLACE() เพื่อแก้ไขสตริงในตาราง ไวยากรณ์ของคิวรีจะเป็นดังนี้ โดย:

• column_name: คอลัมน์ที่ใช้ค้นหาการปรากฏของสตริงย่อย

• old_substring: สตริงย่อยที่ต้องการแทนที่

• new_substring: สตริงย่อยที่จะใช้แทนของเดิม

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

ตัวอย่างการใช้ REPLACE()

ฟังก์ชัน REPLACE() ใน SQL จะแก้ไขหรือแทนที่การปรากฏของสตริงทั้งหมดด้วยสตริงย่อยหรือสตริงที่กำหนด ตัวอย่างด้านล่าง ฟังก์ชัน REPLACE() ได้แทนที่ค่า old ด้วย new ในคอลัมน์ description คิวรีจะประมวลผลทีละแถวเพื่อแทนที่สตริงเดิมด้วยสตริงใหม่

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

หมายเหตุ: ฐานข้อมูล MySQL, PostgreSQL, Oracle และ SQL Server รองรับฟังก์ชัน REPLACE() ทั้งหมด

กรณีการใช้งาน REPLACE() ใน SQL

มีกรณีการใช้งานสำคัญมากมายของฟังก์ชัน REPLACE() สำหรับการจัดการข้อความและการแปลงข้อมูล ได้แก่:

การแทนที่พื้นฐาน

สามารถใช้ฟังก์ชัน REPLACE() เพื่อทำการแทนที่พื้นฐาน สมมติว่ามีตารางต่อไปนี้ในฐานข้อมูล SQL Server ของเรา:

ตัวอย่างตาราง Products ภาพโดยผู้เขียน

ด้วยคิวรี SQL ต่อไปนี้ สามารถใช้ฟังก์ชัน REPLACE() เพื่อเปลี่ยนสถานะ old เป็น new

SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

ตารางผลลัพธ์จะมีลักษณะดังที่แสดงด้านล่าง

ตารางที่อัปเดตด้วย SQL REPLACE() ภาพโดยผู้เขียน

การแทนที่หลายสตริงย่อย

หากต้องการแทนที่หลายสตริงย่อย ให้เชนฟังก์ชัน REPLACE() หลายชุด ที่นี่เราจะอัปเดตค่า old เป็น outdated และ new เป็น fresh

-- Chain multiple REPLACE() functions to replace 'old' with 'outdated' and 'new' with 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

ตารางผลลัพธ์แสดงดังภาพด้านล่าง

ตารางที่อัปเดตโดยใช้หลายสตริงย่อย ภาพโดยผู้เขียน

การลบอักขระ

ส่งสตริงว่างเป็นอาร์กิวเมนต์ตัวที่สามเพื่อเอาอักขระออกทั้งหมด วิธีนี้เป็นวิธีที่สะอาดที่สุดในการลบเครื่องหมายวรรคตอน ช่องว่างเกิน หรืออักขระจัดรูปแบบออกจากคอลัมน์

-- Remove dashes from phone numbers
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

เช่น '555-867-5309' จะกลายเป็น '5558675309' เทคนิคเดียวกันนี้ใช้ลบอักขระพิเศษ สิ่งตกค้างจากการเข้ารหัส หรือช่องว่างที่ไม่ต้องการจากข้อมูลที่นำเข้าได้

การจัดการความไวต่ออักษรพิมพ์เล็ก-ใหญ่

ความไวต่ออักษรพิมพ์เล็ก-ใหญ่ใน REPLACE() ขึ้นอยู่กับฐานข้อมูลและ collation โดยค่าเริ่มต้น PostgreSQL จะถือว่าแยกแยะตัวพิมพ์ ดังนั้น REPLACE(col, 'Old', 'New') จะไม่ตรงกับ 'old' ส่วน SQL Server และ MySQL จะยึดตาม collation ของคอลัมน์หรือฐานข้อมูล—การติดตั้งส่วนใหญ่ใช้ collation ที่ไม่แยกแยะตัวพิมพ์ ดังนั้น REPLACE() จะจับคู่โดยไม่คำนึงถึงตัวพิมพ์

เมื่ออยากแทนค่าที่ไม่แยกแยะตัวพิมพ์ ควรใช้ฟังก์ชัน UPPER() หรือ LOWER() เพื่อแปลงข้อความก่อนการแทนที่ เทคนิคนี้ช่วยให้ค่ามีความสม่ำเสมอ

-- Replace 'OLD' with 'OUTDATED', handling case sensitivity by converting to upper case
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

ตารางที่อัปเดตโดยใช้ฟังก์ชัน REPLACE() และ UPPER() ภาพโดยผู้เขียน

-- Replace 'old' with 'outdated', handling case sensitivity by converting to lowercase
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

ตารางที่อัปเดตโดยใช้ฟังก์ชัน SQL REPLACE() และ LOWER() ภาพโดยผู้เขียน

เทคนิคขั้นสูง

เทคนิคเหล่านี้ผสาน REPLACE() กับฟังก์ชัน SQL อื่นเพื่อจัดการการแทนที่ที่ซับซ้อนกว่าการค้นหาแล้วสลับอย่างง่าย

การแทนที่ด้วยค่าแบบไดนามิก

ฟังก์ชัน REPLACE() สามารถใช้ทำการแทนที่แบบไดนามิกได้โดยผสานกับฟังก์ชัน SQL อื่น ตัวอย่างเช่น ด้วยฟังก์ชัน REPLACE() สามารถใช้ค่าจากอีกคอลัมน์เพื่อแทนที่สตริงย่อยได้ ในตัวอย่างด้านล่าง ฟังก์ชัน REPLACE() แทนที่คำว่า old ของสินค้าแต่ละรายการด้วยค่าจริงจาก product_name

-- Replace 'old' with the value from product_name column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

ตารางที่อัปเดตโดยใช้ฟังก์ชัน REPLACE() และค่าแบบไดนามิก ภาพโดยผู้เขียน

การใช้ REPLACE() ในคำสั่ง UPDATE

ฟังก์ชัน REPLACE() ของ SQL ทำงานภายในคำสั่ง UPDATE เพื่อแก้ไขค่าคอลัมน์ในหลายแถว คิวรีด้านล่างจะแทนที่ค่า old ด้วย outdated ในคอลัมน์ status

-- Update the status column to replace 'old' with 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

สำหรับสถานการณ์ที่ซับซ้อนกว่า เช่น การอัปเดตตารางด้วยการ JOIN กับอีกตาราง ดูบทความสอน SQL UPDATE with JOIN ของเรา

ผสาน REPLACE() กับฟังก์ชันสตริงอื่น

เทคนิคขั้นสูงอย่างหนึ่งคือการผสาน REPLACE() กับฟังก์ชันสตริงอื่นของ SQL เช่น CHARINDEX() หรือ SUBSTRING() เมื่อทำการแทนค่าของสตริง

-- Replace part of the product name starting from the first space with 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extract the substring of product_name starting from the first space to the end
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

ตารางผลลัพธ์จากคิวรีข้างต้นแสดงดังนี้

ตารางที่อัปเดตโดยใช้ REPLACE() และฟังก์ชันสตริงอื่น ภาพโดยผู้เขียน

การแทนที่ค่า NULL ด้วย 0

สามารถใช้ฟังก์ชัน COALESCE() เพื่อแทนค่าที่เป็น NULL ด้วยค่ากันชนก่อนเรียก REPLACE() ดังตัวอย่างด้านล่าง

-- Replace NULL with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

ข้อควรระวัง: หากอาร์กิวเมนต์ ใดๆ ที่ส่งให้ REPLACE() เป็น NULL ฟังก์ชันจะคืนค่า NULL สำหรับแถวนั้น—แม้อาร์กิวเมนต์อื่นจะถูกต้องก็ตาม ห่อคอลัมน์ที่อาจเป็นค่าว่างด้วย COALESCE() ก่อน เรียก REPLACE() ไม่ใช่หลังจากนั้น

การประยุกต์ใช้ฟังก์ชัน SQL REPLACE() ในทางปฏิบัติ

ต่อไปนี้คือสถานการณ์ที่ REPLACE() มีบทบาทในคิวรีใช้งานจริง

• การทำความสะอาดข้อมูล: ฟังก์ชัน REPLACE() มีประโยชน์ในการทำความสะอาดข้อมูล เอาอักขระหรือช่องว่างที่ไม่ต้องการออก และแทนที่ข้อความในชุดข้อมูล

• การแปลงข้อมูล: ฟังก์ชัน REPLACE() ใช้ในการแปลงข้อมูลเพื่อเปลี่ยนค่าให้อยู่ในรูปแบบอื่น และแทนค่าที่ล้าสมัยด้วยค่าใหม่

• การจัดการข้อมูลจากผู้ใช้: ฟังก์ชัน REPLACE() มีความสำคัญในการปรับและแปลงข้อมูลที่ผู้ใช้ป้อนให้เป็นมาตรฐานเดียวกันที่ยอมรับได้ในฐานข้อมูลหนึ่งๆ เทคนิคนี้ช่วยให้ฐานข้อมูลมีความสม่ำเสมอ

• การทำให้ข้อความเป็นมาตรฐาน: ฟังก์ชัน REPLACE() ช่วยทำให้ค่าภายในคอลัมน์สตริงเป็นมาตรฐานเพื่อความสม่ำเสมอ เช่น การย่อคำให้เป็นตัวพิมพ์เล็กหรือใหญ่

• การแทนที่ตามเงื่อนไข: ฟังก์ชัน REPLACE() ยังมีประโยชน์สำหรับการจัดการฐานข้อมูลอย่างง่ายเพื่อแทนค่าตามกฎที่กำหนด

การใช้งาน REPLACE() ตามฐานข้อมูลต่างๆ

ฐานข้อมูล MySQL, PostgreSQL, Oracle และ SQL Server ต่างรองรับฟังก์ชัน REPLACE() ใน SQL ซึ่งมีไวยากรณ์คล้ายกันในฐานข้อมูลเหล่านี้

-- Replace 'old' with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

การพิจารณาด้านประสิทธิภาพและแนวปฏิบัติที่ดีของ REPLACE()

เมื่อใช้ฟังก์ชัน REPLACE() ควรพิจารณาประเด็นด้านประสิทธิภาพและแนวปฏิบัติที่ดีดังต่อไปนี้

ผลกระทบต่อประสิทธิภาพคิวรี

ฟังก์ชัน REPLACE() อาจกระทบต่อประสิทธิภาพคิวรี โดยเฉพาะเมื่อจัดการชุดข้อมูลขนาดใหญ่ ปัญหานี้เกิดขึ้นเพราะฟังก์ชันจะอัปเดตตารางสำหรับแต่ละแถวและอาจใช้เวลานานในการประมวลผล

ข้อพิจารณาเรื่องดัชนี

สร้างดัชนีให้คอลัมน์ที่ใช้ในเงื่อนไขก่อนรัน REPLACE() ใน WHERE หรือ UPDATE หากไม่มีดัชนี ฐานข้อมูลจะสแกนทุกแถวเพื่อค้นหาที่ตรงกัน—ซึ่งมีต้นทุนสูงเป็นพิเศษบนตารางขนาดใหญ่

การติดตามและแก้คอขวดของประสิทธิภาพ

เพื่อแก้ปัญหาที่เกี่ยวข้องกับประสิทธิภาพคิวรี ควรพิจารณาแนวปฏิบัติดังต่อไปนี้:

• ใช้เครื่องมือโปรไฟลิงฐานข้อมูลเพื่อติดตามประสิทธิภาพคิวรีและปรับให้เหมาะสมเมื่อจัดการชุดข้อมูลขนาดใหญ่

• เมื่อใช้ REPLACE() เพื่อแก้ไขค่าสตริง ควรบำรุงรักษาดัชนีอย่างสม่ำเสมอเพื่อให้สอดคล้องกัน

• ใช้การประมวลผลแบบเป็นชุดเพื่อลดเวลาในการรันคิวรีเมื่อจัดการชุดข้อมูลขนาดใหญ่

หากต้องการเรียนรู้ฟังก์ชัน SQL ขั้นสูงอื่นๆ สำหรับการแปลงข้อมูล แนะนำหลักสูตร Learn SQL ของ DataCamp และหลักสูตร Reporting in SQL ที่ครอบคลุมหัวข้อการเพิ่มประสิทธิภาพฐานข้อมูล ช่วยให้เรียนรู้เทคนิคต่างๆ เพื่อปรับปรุงประสิทธิภาพ

ข้อคิดส่งท้าย

ฟังก์ชัน REPLACE() ครอบคลุมความต้องการแทนที่ข้อความส่วนใหญ่ในฐานข้อมูล SQL โปรดคำนึงถึงพฤติกรรมของ NULL—อาร์กิวเมนต์ใดเป็น NULL จะได้ผลลัพธ์เป็น NULL—และตรวจสอบการตั้งค่า collation ของฐานข้อมูลเมื่อความไวต่ออักษรพิมพ์เล็ก-ใหญ่มีความสำคัญ

สามารถเรียนรู้เพิ่มเติมเกี่ยวกับฟังก์ชัน REPLACE() ใน SQL ได้จากหลักสูตร Intermediate SQL และ SQL Fundamentals ของ DataCamp นอกจากนี้ แนะนำเส้นทางอาชีพ Associate Data Analyst in SQL เพื่อเรียนรู้การประยุกต์ใช้ SQL ในการแปลงข้อมูลและเริ่มต้นอาชีพ สุดท้าย แนะนำให้สอบ SQL Associate Certification ของ DataCamp เพื่อแสดงความสำเร็จด้านวิชาชีพในการใช้ SQL เพื่อการวิเคราะห์ข้อมูลและโดดเด่นในระหว่างการสัมภาษณ์งาน

สุดท้าย หากสนใจเรียนรู้เพิ่มเติมเกี่ยวกับฟังก์ชัน SQL ต่างๆ ที่ใช้ในการวิเคราะห์ข้อมูล แนะนำให้ดูบทเรียนอื่นๆ ของฉัน ได้แก่:

• วิธีใช้ตัวดำเนินการ SQL BETWEEN
• วิธีใช้คำสั่ง SQL LIMIT
• วิธีใช้ตัวดำเนินการ SQL IN
• วิธีใช้คำสั่ง SQL OFFSET