פונקציות מחרוזת ב-SQLite: SUBSTR, REPLACE, INSTR ועוד

רוב השאילתות האמיתיות עוסקות במחרוזות

מספרים הם קלים. מחרוזות הן המקום שבו שאילתות מסתבכות: שמות עם רווחים מיותרים, כתובות אימייל באותיות מעורבות, מזהים שמודבקים יחד עם מקפים, שדות טקסט חופשי שכמעט תואמים אבל לא בדיוק. SQLite מגיע עם סט קטן וממוקד של פונקציות מחרוזת שמטפלות ברוב המקרים האלה בלי צורך בקוד אפליקציה.

הדף הזה עובר על הפונקציות שתשתמשו בהן ראשונות: שרשור, חיתוך, חיפוש, החלפה, קיצוץ רווחים ועיצוב.

שרשור משתמש ב-||, לא ב-CONCAT

ל-SQLite אין פונקציית CONCAT. מחרוזות מחוברות בעזרת האופרטור ||:

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

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

מספרים וטיפוסים אחרים מומרים לטקסט אוטומטית. המלכודת: אם אחד האופרנדים הוא NULL, כל הביטוי הופך ל-NULL. זו התנהגות SQL סטנדרטית, אבל היא מפתיעה אנשים:

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

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

עטפו עמודות שעשויות להכיל NULL ב-COALESCE(col, '') או ב-COALESCE(col, 'default') כשאתם רוצים שערך חסר לא ימחק את כל המחרוזת.

Length, Upper, Lower

שלוש הפונקציות שתשתמשו בהן כל הזמן:

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

LENGTH מחזירה את מספר התווים בטקסט, לא את מספר הבייטים. אם אתם באמת רוצים בייטים (נדיר, אבל שימושי לניתוח אחסון), השתמשו ב-OCTET_LENGTH. UPPER ו-LOWER משנות רק אותיות ASCII כברירת מחדל: תווים עם סימני הטעמה עוברים ללא שינוי, אלא אם טענתם את הרחבת ICU.

SUBSTR: חיתוך מחרוזות

SUBSTR(text, start, length) מחלצת חלק ממחרוזת. האינדקסים מתחילים מ-1: 1 הוא התו הראשון, לא 0:

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

כמה דברים שכדאי לזכור:

• הארגומנט השלישי אופציונלי. בלעדיו, מקבלים את כל מה שמ-start ועד הסוף.
• start שלילי נספר מסוף המחרוזת.
• אם start נמצא אחרי הסוף, מקבלים מחרוזת ריקה, לא שגיאה.

גם SUBSTRING מתקבלת כשם נרדף, למקרה שהזיכרון של האצבעות שלכם מגיע ממסד נתונים אחר.

INSTR: מציאת תת מחרוזת

INSTR(haystack, needle) מחזירה את המיקום (החל מ-1) של ההופעה הראשונה של needle בתוך haystack, או 0 אם היא לא נמצאה:

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;

הביטוי האחרון הוא הדרך המקובלת ב-SQLite לקבל "כל מה שלפני ה-@": מוצאים את התו המפריד עם INSTR, ואז חותכים עם SUBSTR. תכתבו את השילוב הזה הרבה. שימו לב שמכיוון ש-INSTR מחזירה 0 כשאין התאמה, כדאי לבדוק לפני החיתוך: העברת 0 ל-SUBSTR נותנת בשקט תוצאות מוזרות.

REPLACE: החלפת תת מחרוזת אחת באחרת

REPLACE(text, old, new) מחליפה כל הופעה של old ב-new:

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

היא רגישה לאותיות גדולות וקטנות ולא מקבלת ביטוי רגולרי, רק תת מחרוזת מילולית. לשינויים מורכבים יותר אפשר לשרשר קריאות REPLACE, אבל אחרי שתיים או שלוש קריאות מקוננות הגיע הזמן לעשות את העבודה באפליקציה.

TRIM, LTRIM, RTRIM

נתונים שמשתמשים מקלידים נוטים להגיע עם רווחים בקצוות. TRIM מסירה אותם:

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

כברירת מחדל הן מסירות רווחים. העבירו ארגומנט שני כדי לקבוע אילו תווים להסיר: כל תו בארגומנט השני נחשב חבר ב"קבוצת תווים להסרה", ולא כתת מחרוזת מילולית. כך TRIM('xxxhelloxx', 'x') נותנת 'hello'.

printf: עיצוב מספרים ומחרוזות

כשצריך מחרוזת מעוצבת, כמו מספר קבוע של ספרות אחרי הנקודה, מספרים עם ריפוד או פלט הקסדצימלי, printf (שנכתבת גם format) עושה את העבודה:

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, כלומר %d, %s, %f, %x, ריפוד באפסים או ברווחים, וכן הלאה. זה נקי בהרבה מבניית מחרוזות עם || וערימה של CAST.

LIKE מול GLOB: התאמת תבניות

שני אופרטורים, שני עולמות שונים.

LIKE משתמש בתווים הכלליים הקלאסיים של SQL, % לכל רצף תווים ו-_ לתו בודד, והוא לא רגיש לאותיות גדולות וקטנות ב-ASCII:

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

GLOB משתמש בתווים הכלליים של מעטפת Unix, * לכל רצף, ? לתו בודד ו-[abc] למחלקות תווים, והוא רגיש לאותיות גדולות וקטנות:

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

הכלל לבחירה ביניהם: LIKE להתאמה בסגנון אנושי כמו "מתחיל ב", "מכיל", "מסתיים ב". GLOB כשרגישות לאותיות חשובה או כשצריך מחלקות תווים. שניהם יכולים להשתמש באינדקסים, אבל רק כשהתבנית מעוגנת להתחלה ('foo%', לא '%foo'): תו כללי בהתחלה מאלץ סריקה מלאה.

פיצול מחרוזות: אין SPLIT

SQLite לא מגיע עם פונקציית SPLIT_STRING. שני פתרונות מעשיים:

-- 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;

לפיצול לפי תו מפריד לכמה שורות, הדרך הנקייה ביותר היא json_each על מערך JSON, או CTE רקורסיבי. נכסה את שניהם בפרקים הבאים. בינתיים, פשוט דעו ש"תן לי כל מילה" היא לא שורה אחת ב-SQLite.

דוגמה מעשית: ניקוי שמות

נחבר הכול יחד. דמיינו טבלת users עם שמות תצוגה מבולגנים: רווחים מיותרים, אותיות מעורבות ותארים אופציונליים כמו "Dr. " או "Mr. " שתרצו להסיר:

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;

קוראים את הביטוי מבפנים החוצה: מסירים רווחים חיצוניים, ממירים לאותיות קטנות, מוחקים את התארים, ומקצצים שוב למקרה שהסרת התואר השאירה רווח בהתחלה. כל שלב הוא פונקציה אחת: המורכבות נובעת מהערימה שלהן. כשהערימה עוברת שלוש או ארבע רמות, זה רמז להשתמש בעמודה מחושבת (פרק: תכונות מתקדמות) או לבצע את הניקוי בזמן ייבוא הנתונים.

מה לקחת מכאן

• || לשרשור. NULL מרעיל את התוצאה, אז השתמשו ב-COALESCE.
• SUBSTR ו-INSTR יחד מכסות את רוב הצרכים של "מצא וחתוך".
• REPLACE מחליפה כל הופעה של תת מחרוזת מילולית.
• TRIM והפונקציות הדומות לה מקבלות קבוצת תווים מותאמת, לא רק רווחים.
• printf היא הכלי הנכון לפלט מעוצב.
• LIKE לתווים כלליים של SQL ללא רגישות לאותיות, GLOB לתבניות בסגנון מעטפת עם רגישות לאותיות.

הבא בתור: פונקציות מספריות

אחרי המחרוזות, התחנה הבאה הטבעית היא מספרים: עיגול, ערכים מוחלטים, מוזרויות של חילוק, ופונקציות המתמטיקה ש-SQLite הוסיף בגרסאות האחרונות. זה הדף הבא.