Cách sử dụng hàm SQL REPLACE()

Tóm tắt nhanh

• REPLACE(string, old_substring, new_substring) thay thế mọi lần xuất hiện của một chuỗi con chỉ trong một lần gọi

• Gọi lồng nhau để thay thế nhiều chuỗi con: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Truyền vào chuỗi rỗng để loại bỏ ký tự: REPLACE(phone, '-', '')

• Nếu bất kỳ đối số nào là NULL, hàm sẽ trả về NULL cho hàng đó

• Phân biệt chữ hoa/thường phụ thuộc vào cơ sở dữ liệu và collation của bạn—SQL Server và MySQL mặc định không phân biệt; PostgreSQL mặc định có phân biệt

Tìm hiểu hàm SQL REPLACE()

Hàm REPLACE() trong SQL tìm kiếm chuỗi con hoặc chuỗi được chỉ định trong một cột và thay thế nó bằng chuỗi khác. Ví dụ dưới đây cho thấy cách sử dụng cơ bản của REPLACE(). Các đối số trong hàm gồm có:

• string: Chuỗi gốc nơi bạn sẽ thực hiện việc thay thế.

• old_substring: Chuỗi con cần được thay thế.

• new_substring: Chuỗi con dùng để thay thế chuỗi cũ.

REPLACE(string, old_substring, new_substring)

Khi dùng REPLACE() để thay đổi các chuỗi trong một bảng, truy vấn sẽ có cú pháp sau, trong đó:

• column_name: Cột dùng để tìm các lần xuất hiện của chuỗi con.

• old_substring: Chuỗi con cần được thay thế.

• new_substring: Chuỗi con dùng để thay thế chuỗi cũ.

-- Chọn cột description từ bảng products,
-- thay thế các lần xuất hiện của 'old' bằng 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Ví dụ về REPLACE()

Hàm REPLACE() trong SQL thay đổi hoặc thay thế tất cả các lần xuất hiện chuỗi bằng một chuỗi con hoặc chuỗi được chỉ định. Trong ví dụ dưới đây, REPLACE() đã thay thế giá trị old bằng new trong cột description. Truy vấn sẽ thực thi trên từng hàng để thay chuỗi cũ bằng chuỗi mới.

-- Chọn cột description từ bảng products,
-- thay thế các lần xuất hiện của 'old' bằng 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Lưu ý: Các cơ sở dữ liệu MySQL, PostgreSQL, Oracle và SQL Server đều hỗ trợ hàm REPLACE().

Các trường hợp sử dụng REPLACE() trong SQL

Có nhiều trường hợp sử dụng quan trọng của hàm REPLACE() trong thao tác văn bản và chuyển đổi dữ liệu. Bao gồm:

Thay thế cơ bản

Bạn có thể dùng REPLACE() để thực hiện thay thế cơ bản. Giả sử chúng ta có bảng sau trong cơ sở dữ liệu SQL Server:

Ví dụ bảng Products. Ảnh: Tác giả.

Sử dụng truy vấn SQL sau, bạn có thể dùng REPLACE() để chuyển trạng thái old thành new.

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

Bảng kết quả sẽ trông như dưới đây.

Bảng được cập nhật bằng SQL REPLACE(). Ảnh: Tác giả.

Thay thế nhiều chuỗi con

Nếu muốn thay thế nhiều chuỗi con, hãy liên kết nhiều hàm REPLACE(). Ở đây, chúng ta cập nhật giá trị old thành outdated, và new thành fresh.

-- Liên kết nhiều REPLACE() để thay 'old' bằng 'outdated' và 'new' bằng 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

Bảng kết quả được hiển thị dưới đây.

Bảng được cập nhật bằng nhiều chuỗi con. Ảnh: Tác giả.

Loại bỏ ký tự

Truyền chuỗi rỗng làm đối số thứ ba để loại bỏ hoàn toàn các ký tự. Đây là cách gọn gàng nhất để loại dấu câu, khoảng trắng thừa hoặc ký tự định dạng khỏi một cột.

-- Loại dấu gạch trong số điện thoại
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

Ví dụ, '555-867-5309' sẽ thành '5558675309'. Cùng kỹ thuật này loại bỏ ký tự đặc biệt, lỗi mã hóa, hoặc khoảng trắng không mong muốn từ dữ liệu nhập khẩu.

Xử lý phân biệt chữ hoa/thường

Việc phân biệt chữ hoa/thường trong REPLACE() phụ thuộc vào cơ sở dữ liệu và collation. PostgreSQL mặc định phân biệt chữ hoa/thường, nên REPLACE(col, 'Old', 'New') sẽ không khớp với 'old'. SQL Server và MySQL tuân theo collation của cột hoặc cơ sở dữ liệu—đa số cài đặt mặc định là không phân biệt, nên REPLACE() sẽ khớp bất kể chữ hoa/thường.

Khi bạn muốn thay các giá trị mà không phân biệt chữ hoa/thường, hãy dùng UPPER() hoặc LOWER() để chuyển đổi văn bản trước khi thay thế. Kỹ thuật này giúp giá trị nhất quán.

-- Thay 'OLD' bằng 'OUTDATED', xử lý phân biệt chữ bằng cách chuyển sang chữ hoa
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

Bảng cập nhật bằng các hàm REPLACE() và UPPER(). Ảnh: Tác giả.

-- Thay 'old' bằng 'outdated', xử lý phân biệt chữ bằng cách chuyển sang chữ thường
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

Bảng cập nhật bằng các hàm SQL REPLACE() và LOWER(). Ảnh: Tác giả.

Kỹ thuật nâng cao

Các kỹ thuật này kết hợp REPLACE() với các hàm SQL khác để xử lý việc thay thế vượt ra ngoài tìm-và-thay đơn giản.

Thay thế bằng giá trị động

Hàm REPLACE() có thể dùng để thực hiện thay thế động bằng cách kết hợp với các hàm SQL khác. Ví dụ, với REPLACE(), bạn có thể dùng giá trị của cột khác để thay thế chuỗi con. Ở ví dụ dưới đây, REPLACE() thay mỗi sản phẩm cũ bằng giá trị thực tế từ product_name.

-- Thay 'old' bằng giá trị từ cột product_name
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Bảng cập nhật bằng hàm REPLACE() và giá trị động. Ảnh: Tác giả.

Dùng REPLACE() trong câu lệnh UPDATE

Hàm REPLACE() hoạt động trong các câu lệnh UPDATE để sửa đổi giá trị cột trên một hoặc nhiều hàng. Truy vấn dưới đây thay giá trị old bằng outdated trong cột status.

-- Cập nhật cột status để thay 'old' bằng 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Với các tình huống phức tạp hơn, như cập nhật bảng bằng cách JOIN với bảng khác, xem hướng dẫn SQL UPDATE with JOIN của chúng tôi.

Kết hợp REPLACE() với các hàm chuỗi khác

Một kỹ thuật nâng cao là kết hợp REPLACE() với các hàm chuỗi SQL khác, như CHARINDEX() hoặc SUBSTRING(), khi thay giá trị chuỗi.

-- Thay phần của tên sản phẩm bắt đầu từ dấu cách đầu tiên bằng 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Trích chuỗi con của product_name bắt đầu từ dấu cách đầu tiên đến hết
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

Bảng kết quả từ truy vấn trên được hiển thị dưới đây.

Bảng cập nhật bằng REPLACE() và các hàm chuỗi khác. Ảnh: Tác giả.

Thay thế NULL bằng 0

Bạn có thể dùng hàm COALESCE() để thay giá trị NULL bằng giá trị dự phòng trước khi gọi REPLACE(), như dưới đây.

-- Thay NULL bằng 'new' trong cột status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

Lưu ý quan trọng: Nếu bất kỳ đối số nào truyền vào REPLACE() là NULL, hàm sẽ trả về NULL cho hàng đó—kể cả khi các đối số khác hợp lệ. Hãy bọc các cột có thể NULL trong COALESCE() trước khi gọi REPLACE(), không phải sau đó.

Ứng dụng thực tế của hàm SQL REPLACE()

Dưới đây là các kịch bản nơi REPLACE() xứng đáng có mặt trong truy vấn sản xuất.

• Làm sạch dữ liệu: REPLACE() hữu ích để làm sạch dữ liệu, loại bỏ và thay thế các ký tự hoặc khoảng trắng không mong muốn trong chuỗi của tập dữ liệu.

• Chuyển đổi dữ liệu: REPLACE() được dùng trong chuyển đổi dữ liệu để đổi giá trị sang định dạng khác và thay giá trị lỗi thời bằng giá trị mới.

• Xử lý dữ liệu người dùng nhập vào: REPLACE() quan trọng trong việc điều chỉnh và chuyển đổi dữ liệu đầu vào của người dùng về một chuẩn thống nhất phù hợp với cơ sở dữ liệu cụ thể. Kỹ thuật này giúp đảm bảo tính đồng nhất.

• Chuẩn hóa văn bản: REPLACE() chuẩn hóa các giá trị trong cột chuỗi để đảm bảo đồng nhất, chẳng hạn viết tắt ở dạng chữ thường hoặc chữ hoa.

• Thay thế có điều kiện: REPLACE() cũng hữu ích cho các thực hành quản trị cơ sở dữ liệu đơn giản nhằm thay giá trị theo các quy tắc cụ thể.

Triển khai REPLACE() theo từng hệ cơ sở dữ liệu

MySQL, PostgreSQL, Oracle và SQL Server đều hỗ trợ hàm REPLACE() trong SQL, với cú pháp tương tự nhau trên các hệ này.

-- Thay 'old' bằng 'new' trong cột status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Cân nhắc hiệu năng và thực tiễn tốt khi dùng REPLACE()

Khi sử dụng REPLACE(), cần lưu ý các điểm về hiệu năng và thực tiễn tốt sau.

Tác động tới hiệu năng truy vấn

Hàm REPLACE() có thể ảnh hưởng đến hiệu năng truy vấn, đặc biệt khi xử lý tập dữ liệu lớn. Nguyên nhân là vì hàm cập nhật bảng theo từng hàng và có thể mất thời gian để truy vấn thực thi.

Cân nhắc về đánh chỉ mục

Đánh chỉ mục cho cột bạn dùng để truy vấn trước khi chạy REPLACE() trong WHERE hoặc UPDATE. Nếu không có chỉ mục, cơ sở dữ liệu sẽ quét mọi hàng để tìm khớp—cực kỳ tốn kém trên các bảng lớn.

Giám sát và xử lý nút thắt hiệu năng

Để khắc phục các vấn đề liên quan đến hiệu năng truy vấn, hãy cân nhắc các thực tiễn tốt sau:

• Sử dụng công cụ profiling của cơ sở dữ liệu để giám sát hiệu năng truy vấn và tối ưu khi xử lý tập dữ liệu lớn.

• Khi dùng REPLACE() để sửa đổi giá trị chuỗi, hãy bảo trì chỉ mục thường xuyên để đảm bảo chúng phù hợp.

• Thực hiện xử lý theo lô (batch) để giảm thời gian thực thi truy vấn khi xử lý tập dữ liệu lớn.

Để học thêm các hàm SQL nâng cao phục vụ chuyển đổi dữ liệu, tôi khuyến nghị khóa Learn SQL của DataCamp. Khóa Reporting in SQL cũng đề cập các chủ đề về tối ưu hóa cơ sở dữ liệu, giúp bạn học các kỹ thuật khác nhau để cải thiện hiệu năng.

Tổng kết

Hàm REPLACE() đáp ứng hầu hết nhu cầu thay thế văn bản trên các hệ cơ sở dữ liệu SQL. Hãy lưu ý hành vi với NULL—bất kỳ đối số NULL nào cũng trả về NULL—và kiểm tra cài đặt collation của cơ sở dữ liệu khi vấn đề phân biệt chữ hoa/thường là quan trọng.

Bạn có thể tìm hiểu thêm về REPLACE() trong SQL qua các khóa Intermediate SQL và SQL Fundamentals của DataCamp. Tôi cũng khuyến nghị lộ trình nghề nghiệp Associate Data Analyst in SQL để tìm hiểu thêm về ứng dụng của SQL trong chuyển đổi dữ liệu và khởi đầu sự nghiệp. Cuối cùng, tôi khuyến khích bạn tham gia SQL Associate Certification của DataCamp để chứng minh thành tựu chuyên môn trong phân tích dữ liệu bằng SQL và nổi bật trong các buổi phỏng vấn.

Cuối cùng, nếu bạn muốn tìm hiểu thêm về các hàm SQL dùng trong phân tích dữ liệu, tôi khuyến nghị xem thêm một số hướng dẫn khác của tôi, bao gồm:

• Cách sử dụng toán tử SQL BETWEEN
• Cách sử dụng mệnh đề SQL LIMIT
• Cách sử dụng toán tử SQL IN
• Cách sử dụng mệnh đề SQL OFFSET