Hàm chuỗi trong SQLite: SUBSTR, REPLACE, INSTR và hơn nữa

Phần lớn truy vấn thực tế xoay quanh chuỗi

Số thì dễ. Chuỗi mới là chỗ truy vấn trở nên lộn xộn: tên có khoảng trắng thừa, email lẫn chữ hoa chữ thường, ID dính liền với nhau bằng dấu gạch ngang, các trường văn bản tự do gần khớp mà không hẳn khớp. SQLite có sẵn một bộ hàm chuỗi (string function) nhỏ gọn, tập trung, xử lý được phần lớn những việc này mà không cần đến code ứng dụng.

Trang này đi qua những hàm bạn sẽ dùng đầu tiên: nối, cắt, tìm kiếm, thay thế, cắt khoảng trắng và định dạng.

Nối chuỗi dùng ||, không phải CONCAT

SQLite không có hàm CONCAT. Các chuỗi được nối bằng toán tử ||:

SELECT 'Hello, ' || 'world!' AS greeting;

SELECT 'User #' || 42 || ' joined' AS message;

Số và các kiểu khác được tự động chuyển sang văn bản. Điểm cần lưu ý: nếu có toán hạng nào là NULL, cả biểu thức trở thành NULL. Đó là hành vi chuẩn của SQL, nhưng vẫn khiến nhiều người bất ngờ:

SELECT 'Hello, ' || NULL || '!' AS greeting;

SELECT 'Hello, ' || COALESCE(NULL, 'friend') || '!' AS safe_greeting;

Hãy bọc các cột có thể NULL trong COALESCE(col, '') hoặc COALESCE(col, 'default') khi bạn không muốn một giá trị bị thiếu xóa sổ cả chuỗi.

Length, Upper, Lower

Ba hàm bạn sẽ dùng liên tục:

SELECT
    LENGTH('résumé')        AS char_count,
    UPPER('hello world')    AS upper_case,
    LOWER('HELLO WORLD')    AS lower_case;

LENGTH trả về số ký tự của văn bản, không phải số byte. Nếu bạn thực sự cần số byte (hiếm, nhưng hữu ích khi phân tích dung lượng lưu trữ), hãy dùng OCTET_LENGTH. Theo mặc định, UPPER và LOWER chỉ biến đổi chữ cái ASCII: các ký tự có dấu được giữ nguyên, trừ khi bạn đã nạp extension ICU.

SUBSTR: cắt chuỗi

SUBSTR(text, start, length) trích ra một phần của chuỗi. Chỉ số bắt đầu từ 1: 1 là ký tự đầu tiên, không phải 0:

SELECT
    SUBSTR('codecademy', 1, 4)  AS first_four,
    SUBSTR('codecademy', 5)     AS rest,
    SUBSTR('codecademy', -3)    AS last_three;

Vài điều cần nhớ:

• Tham số thứ ba là tùy chọn. Không có nó, bạn nhận mọi thứ từ start đến cuối chuỗi.
• start âm sẽ đếm từ cuối chuỗi.
• Nếu start vượt quá cuối chuỗi, bạn nhận một chuỗi rỗng, không phải lỗi.

SUBSTRING được chấp nhận như một từ đồng nghĩa, phòng khi bạn đã quen tay từ một cơ sở dữ liệu khác.

INSTR: tìm chuỗi con

INSTR(haystack, needle) trả về vị trí (tính từ 1) của lần xuất hiện đầu tiên của needle trong haystack, hoặc 0 nếu không tìm thấy:

SELECT
    INSTR('user@example.com', '@')        AS at_position,
    INSTR('user@example.com', 'xyz')      AS not_found,
    SUBSTR('user@example.com',
           1,
           INSTR('user@example.com', '@') - 1) AS local_part;

Biểu thức cuối cùng là cách viết quen thuộc trong SQLite cho "mọi thứ trước dấu @": tìm ký tự phân cách bằng INSTR, rồi cắt bằng SUBSTR. Bạn sẽ viết tổ hợp này thường xuyên. Để ý rằng INSTR trả về 0 khi không có gì khớp, nghĩa là bạn nên kiểm tra trước khi cắt: đưa 0 vào SUBSTR sẽ lặng lẽ cho ra kết quả kỳ lạ.

REPLACE: thay chuỗi con này bằng chuỗi con khác

REPLACE(text, old, new) thay mọi lần xuất hiện của old bằng new:

SELECT
    REPLACE('555-123-4567', '-', '')                AS digits_only,
    REPLACE('Hello, world. Hello, again.',
            'Hello',
            'Hi')                                   AS shortened,
    REPLACE('  spaced  out  ', ' ', '')             AS no_spaces;

Hàm này phân biệt hoa thường và không nhận regex, chỉ nhận một chuỗi con nguyên văn. Với các phép biến đổi phức tạp hơn, bạn có thể nối nhiều lời gọi REPLACE, nhưng quá hai ba lớp lồng nhau thì đã đến lúc chuyển việc đó sang ứng dụng.

TRIM, LTRIM, RTRIM

Dữ liệu người dùng nhập thường kèm khoảng trắng ở hai đầu. TRIM loại bỏ chúng:

SELECT
    TRIM('   hello   ')              AS both_sides,
    LTRIM('   hello   ')             AS left_only,
    RTRIM('   hello   ')             AS right_only,
    TRIM('---hello---', '-')         AS custom_chars;

Theo mặc định, các hàm này xóa dấu cách. Truyền tham số thứ hai để chỉ định ký tự cần xóa: mỗi ký tự trong tham số thứ hai được coi là một phần tử của "tập cần xóa", không phải một chuỗi con nguyên văn. Vì vậy TRIM('xxxhelloxx', 'x') cho ra 'hello'.

printf: định dạng số và chuỗi

Khi bạn cần một chuỗi có định dạng, như số chữ số thập phân cố định, số có đệm, đầu ra dạng hex, printf (cũng có thể viết là format) sẽ làm việc đó:

SELECT
    printf('%.2f',         3.14159)            AS two_decimals,
    printf('%05d',         42)                 AS padded,
    printf('$%,.2f',       1234567.89)         AS money,
    printf('%s is %d',     'Ada',  30)         AS sentence;

Các ký hiệu định dạng theo quy ước của C, tức là %d, %s, %f, %x, đệm bằng 0 hoặc dấu cách, v.v. Cách này gọn hơn nhiều so với ghép chuỗi bằng || và một đống CAST.

LIKE và GLOB: so khớp mẫu

Hai toán tử, hai thế giới khác nhau.

LIKE dùng các ký tự đại diện kinh điển của SQL: % cho một dãy ký tự bất kỳ, _ cho một ký tự đơn, và không phân biệt hoa thường với ASCII:

SELECT 'Alice'  LIKE 'a%'      AS alice_match;
SELECT 'README' LIKE '_____'   AS five_chars;
SELECT 'foo.txt' LIKE '%.txt'  AS is_text_file;

GLOB dùng ký tự đại diện kiểu Unix shell: * cho một dãy bất kỳ, ? cho một ký tự đơn, [abc] cho lớp ký tự, và có phân biệt hoa thường:

SELECT 'Alice'  GLOB 'A*'         AS alice_match;
SELECT 'Alice'  GLOB 'a*'         AS lowercase_match;
SELECT 'log7'   GLOB 'log[0-9]'   AS digit_class;

Quy tắc chọn giữa hai toán tử: LIKE cho kiểu so khớp tự nhiên như "bắt đầu bằng", "chứa", "kết thúc bằng". GLOB khi cần phân biệt hoa thường hoặc cần lớp ký tự. Cả hai đều có thể dùng index, nhưng chỉ khi mẫu được neo ở đầu ('foo%', không phải '%foo'): ký tự đại diện ở đầu buộc phải quét toàn bộ bảng.

Tách chuỗi: không có SPLIT

SQLite không có hàm SPLIT_STRING. Có hai cách khắc phục thực tế:

-- Pull a single piece using INSTR + SUBSTR
WITH addresses AS (
    SELECT 'rosa@example.com'   AS email UNION ALL
    SELECT 'boris@coddy.tech'   UNION ALL
    SELECT 'ada@lovelace.org'
)
SELECT
    SUBSTR(email, 1, INSTR(email, '@') - 1)  AS local_part,
    SUBSTR(email, INSTR(email, '@') + 1)     AS domain
FROM addresses;

Để tách theo ký tự phân cách thành nhiều hàng, cách gọn nhất là dùng json_each trên một mảng JSON, hoặc một CTE đệ quy. Chúng ta sẽ tìm hiểu cả hai ở các chương sau. Hiện tại, bạn chỉ cần biết rằng "cho tôi từng từ một" không phải là việc viết được trong một dòng ở SQLite.

Ví dụ hoàn chỉnh: làm sạch tên

Kết hợp tất cả lại. Hãy hình dung một bảng users với tên hiển thị lộn xộn: khoảng trắng thừa, chữ hoa chữ thường lẫn lộn, các danh xưng tùy chọn như "Dr. " hay "Mr. " mà bạn muốn loại bỏ:

WITH users(id, display_name) AS (
    VALUES
      (1, '  Dr. Ada Lovelace  '),
      (2, 'mr. Boris Karloff'),
      (3, 'Rosa  Parks'),
      (4, '   alan TURING')
)
SELECT
    id,
    display_name AS original,
    TRIM(
      REPLACE(
        REPLACE(LOWER(TRIM(display_name)), 'dr. ', ''),
        'mr. ', ''
      )
    ) AS cleaned
FROM users;

Biểu thức này đọc từ trong ra ngoài: xóa khoảng trắng hai đầu, chuyển thành chữ thường, bỏ danh xưng, rồi cắt khoảng trắng lần nữa phòng khi việc bỏ danh xưng để lại một dấu cách ở đầu. Mỗi bước chỉ là một hàm; độ phức tạp đến từ việc xếp chồng chúng. Khi chồng lớp vượt quá ba bốn tầng, đó là dấu hiệu nên dùng cột generated (chương Tính năng nâng cao) hoặc làm sạch ngay khi nhập dữ liệu.

Những điều cần nhớ

• || để nối chuỗi; NULL làm hỏng kết quả, nên hãy dùng COALESCE.
• SUBSTR và INSTR kết hợp với nhau đáp ứng hầu hết nhu cầu "tìm rồi cắt".
• REPLACE thay mọi lần xuất hiện của một chuỗi con nguyên văn.
• TRIM và các hàm anh em nhận một tập ký tự tùy chọn, không chỉ khoảng trắng.
• printf là công cụ đúng cho đầu ra có định dạng.
• LIKE cho ký tự đại diện SQL không phân biệt hoa thường, GLOB cho mẫu kiểu shell có phân biệt hoa thường.

Tiếp theo: hàm số học

Xong phần chuỗi, điểm dừng tiếp theo hiển nhiên là số: làm tròn, giá trị tuyệt đối, những điểm lạ của phép chia và các hàm toán học mà SQLite bổ sung ở các phiên bản gần đây. Đó là nội dung của trang tiếp theo.