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

1. כן, יש לנו בעיה של תאימות

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

https://sqliteonline.com

ולנסות את הפקודות על בסיס הנתונים שמתאים לכם.

2. אורך מחרוזת

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

CREATE TABLE paper_clips (
    id INTEGER PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    size VARCHAR(20),
    color VARCHAR(20),
    quantity INTEGER
);

INSERT INTO paper_clips (type, size, color, quantity) VALUES
('standard', 'medium', 'silver', 100),
('binder', 'large', 'black', 50),
('mini', 'small', 'red', 200),
('jumbo', 'extra_large', 'blue', 30),
('colored', 'medium', 'green', 150);

הפונקציה length מחזירה אורך של מחרוזת. אני יכול להפעיל אותה בלי טבלה בכלל באופן הבא:

SELECT LENGTH('hello');

ולקבל 5, או עם טבלה כדי לקבל אורך של אחת העמודות:

select id, type, size, color, quantity, length(size) from paper_clips;

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

אפשר להשתמש ב length גם ב where של השאילתה, למשל לחפש את כל השורות בהן האורך של הצבע של הסיכה גדול מ-4:

select id, type, size, color, quantity from paper_clips where length(color) > 4;

שימו לב להבדל בין length(color) ל length('hello'). כשאני מסתכל על אורך של תוכן בבסיס הנתונים שרשום בעמודה מסוימת אני כותב את שם העמודה בלי גרש סביבו. כשאני רוצה לקבל אורך של מילה קבועה אני חייב להקיף אותה בגרש.

הפקודה length עובדת בבסיסי נתונים SQLite, PostgreSQL, MariaDB. ב SQL Server היא נקראת len.

3. חיבור מחרוזות

ניצור טבלה חדשה של לקוחות עם התוכן הבא:

create table customers (
    id integer primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    email varchar(100) unique not null
);

insert into customers (first_name, last_name, email) values
('john', 'doe', 'john.doe@example.com'),
('jane', 'smith', 'jane.smith@example.com'),
('mike', 'johnson', 'mike.johnson@example.com'),
('emily', 'brown', 'emily.brown@example.com'),
('david', 'wilson', 'david.wilson@example.com');

הפקודה הבאה תחבר את השם הפרטי ושם המשפחה של הלקוח לעמודה אחת, ולידו תדפיס גם את האורך הכולל של השם הפרטי ושם המשפחה:

select
  concat(first_name, ' ', last_name) as name,
  length(concat(first_name, ' ', last_name)) as len
from customers;

שימו לב שה as לא מאפשר לי להשתמש במילה name בהמשך השורה, אבל אני כן יכול להשתמש בפונקציה בתוך פונקציה ולשלב את concat עם length. הפקודה concat מקבלת מספר מחרוזות ומחברת אותן יחד לעמודה אחת.

4. חיתוך חלק ממחרוזת

הפקודה substr מקבלת מחרוזת, נקודת התחלה ואורך וחותכת רק את החלק שביקשנו מתוך המחרוזת. לדוגמה נוכל להפעיל:

select substr('hello', 2, 2)

ונקבל את הפלט el כי זאת המילה שמתחילה באות השנייה ואורכה שתי אותיות מתוך המחרוזת הגדולה יותר hello. בחזרה לטבלת הלקוחות נוכל לקחת רק את האות הראשונה של השם הפרטי ולחבר אותה לשם המשפחה באופן הבא:

select id, concat(last_name, ' ', substr(first_name, 1, 1)) from customers;

הפונקציות upper ו lower מאפשרות להפוך טקסט לאותיות גדולות או קטנות, וכך נוכל לעדכן את אותה אות ראשונה שתופיע באות גדולה:

select id, concat(last_name, ' ', upper(substr(first_name, 1, 1))) from customers;

ומיטיבי לכת יוכלו לעדכן גם את שם המשפחה כך שיתחיל באות גדולה:

select
  id,
  concat(
    concat(upper(substr(last_name, 1, 1)), substr(last_name, 2)),
    ' ',
    upper(substr(first_name, 1, 1))) as name
from customers;

הפונקציה substr עובדת ברוב בסיסי הנתונים אבל ב MS SQL היא נקראת substring.

5. החלפת טקסט במחרוזת

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

select id, email from customers;

ולקבל:

id  email
5   david.wilson@example.com
4   emily.brown@example.com
2   jane.smith@example.com
1   john.doe@example.com
3   mike.johnson@example.com

ואז להשתמש ב replace כדי לעדכן את כל כתובות האימייל מהסיומת example.com לסיומת gmail.com באופן הבא:

update customers set email = replace(email, 'example.com', 'gmail.com');

6. עכשיו אתם

1. עדכנו את טבלת הלקוחות והוסיפו לשם המשפחה של כל לקוח את המספר 5.

2. מחקו את הלקוח david wilson מהטבלה.

3. הדפיסו מעמודת אימייל רק את הטקסט שמופיע לפני הסיומת gmail.com.

4. הציגו רק את השורות בהן אורך כתובת האימייל קצר יותר מ 10 תווים. אין כאלה שורות? הכניסו אחת.