টেক্সট, সংখ্যা ও তারিখের ফাংশন, CAST আর COALESCE

SELECT name,
       UPPER(name)                              AS upper_name,
       LENGTH(name)                             AS len,
       SUBSTR(name, 1, INSTR(name, ' ') - 1)    AS first_name,
       SUBSTR(email, INSTR(email, '@') + 1)     AS domain
FROM customers
WHERE customer_id <= 3
ORDER BY customer_id;

+---------------+---------------+-----+------------+-------------+
| name          | upper_name    | len | first_name | domain      |
+---------------+---------------+-----+------------+-------------+
| Nadia Rahman  | NADIA RAHMAN  |  12 | Nadia      | example.com |
| Tanvir Ahmed  | TANVIR AHMED  |  12 | Tanvir     | example.com |
| Farhana Akter | FARHANA AKTER |  13 | Farhana    | example.com |
+---------------+---------------+-----+------------+-------------+

SELECT '[' || TRIM('  Dhaka ') || ']'                               AS trimmed,
       LOWER(TRIM('  Nadia@Example.COM '))                          AS clean_email,
       REPLACE(REPLACE('+880 1711-223344', ' ', ''), '-', '')       AS phone;

+---------+-------------------+----------------+
| trimmed | clean_email       | phone          |
+---------+-------------------+----------------+
| [Dhaka] | nadia@example.com | +8801711223344 |
+---------+-------------------+----------------+

SELECT name,
       name || ' (' || city || ')'                         AS label,
       name || ' (' || COALESCE(city, 'Unknown') || ')'    AS label_fixed
FROM customers
WHERE customer_id IN (4, 5)
ORDER BY customer_id;

+-----------------+----------------------+---------------------------+
| name            | label                | label_fixed               |
+-----------------+----------------------+---------------------------+
| Rafiq Islam     | Rafiq Islam (Sylhet) | Rafiq Islam (Sylhet)      |
| Sadia Chowdhury | NULL                 | Sadia Chowdhury (Unknown) |
+-----------------+----------------------+---------------------------+

-- MySQL
SELECT 'a' || 'b'                     AS pipes,
       CONCAT('a', 'b')               AS concat_ab,
       CONCAT('a', NULL, 'b')         AS concat_null,
       CONCAT_WS('-', 'a', NULL, 'b') AS with_sep;

+-------+-----------+-------------+----------+
| pipes | concat_ab | concat_null | with_sep |
+-------+-----------+-------------+----------+
|     0 | ab        | NULL        | a-b      |
+-------+-----------+-------------+----------+

-- PostgreSQL
SELECT LEFT(name, 5)                     AS left_5,
       RIGHT(email, 11)                  AS right_11,
       SUBSTRING(name FROM 1 FOR 3)      AS first_3,
       POSITION('@' IN email)            AS at_pos,
       SPLIT_PART(email, '@', 2)         AS domain
FROM customers
WHERE customer_id = 1;

+--------+-------------+---------+--------+-------------+
| left_5 | right_11    | first_3 | at_pos | domain      |
+--------+-------------+---------+--------+-------------+
| Nadia  | example.com | Nad     |      6 | example.com |
+--------+-------------+---------+--------+-------------+

-- MySQL
SELECT LENGTH('ঢাকা') AS length_bytes, CHAR_LENGTH('ঢাকা') AS chars;

+--------------+-------+
| length_bytes | chars |
+--------------+-------+
|           12 |     4 |
+--------------+-------+

SELECT name, price,
       price / 1000                AS k_wrong,
       ROUND(price / 1000.0, 1)    AS k_taka
FROM products
WHERE product_id IN (1, 4, 5)
ORDER BY product_id;

+---------------------+-------+---------+--------+
| name                | price | k_wrong | k_taka |
+---------------------+-------+---------+--------+
| Python Crash Course |  1200 |       1 |    1.2 |
| Wireless Mouse      |   900 |       0 |    0.9 |
| 27-inch Monitor     | 18500 |      18 |   18.5 |
+---------------------+-------+---------+--------+

-- MySQL
SELECT 7 / 2 AS divide, 7 DIV 2 AS int_divide, 7 % 2 AS remainder,
       CEIL(2.1) AS ceil_, FLOOR(-2.1) AS floor_, ROUND(AVG(price), 2) AS avg_price
FROM products;

+--------+------------+-----------+-------+--------+-----------+
| divide | int_divide | remainder | ceil_ | floor_ | avg_price |
+--------+------------+-----------+-------+--------+-----------+
| 3.5000 |          3 |         1 |     3 |     -3 |   5254.55 |
+--------+------------+-----------+-------+--------+-----------+

-- PostgreSQL
SELECT 7 / 2 AS divide, 7 / 2.0 AS decimal_divide, 7 % 2 AS remainder,
       CEIL(2.1) AS ceil_, FLOOR(-2.1) AS floor_, ROUND(AVG(price), 2) AS avg_price
FROM products;

+--------+--------------------+-----------+-------+--------+-----------+
| divide | decimal_divide     | remainder | ceil_ | floor_ | avg_price |
+--------+--------------------+-----------+-------+--------+-----------+
|      3 | 3.5000000000000000 |         1 |     3 |     -3 |   5254.55 |
+--------+--------------------+-----------+-------+--------+-----------+

-- PostgreSQL
SELECT ROUND(CAST(2.345 AS DOUBLE PRECISION), 2);

Error: function round(double precision, integer) does not exist

SELECT order_id, order_date,
       strftime('%Y-%m', order_date)        AS month,
       strftime('%w', order_date)           AS weekday,
       date(order_date, '+7 days')          AS due_date,
       date(order_date, 'start of month')   AS month_start
FROM orders
WHERE order_id IN (1, 7, 13)
ORDER BY order_id;

+----------+------------+---------+---------+------------+-------------+
| order_id | order_date | month   | weekday | due_date   | month_start |
+----------+------------+---------+---------+------------+-------------+
|        1 | 2026-01-12 | 2026-01 | 1       | 2026-01-19 | 2026-01-01  |
|        7 | 2026-03-15 | 2026-03 | 0       | 2026-03-22 | 2026-03-01  |
|       13 | 2026-06-02 | 2026-06 | 2       | 2026-06-09 | 2026-06-01  |
+----------+------------+---------+---------+------------+-------------+

SELECT customer_id, joined_on,
       CAST(julianday('2026-06-30') - julianday(joined_on) AS INTEGER) AS tenure_days
FROM customers
ORDER BY tenure_days DESC, customer_id
LIMIT 4;

+-------------+------------+-------------+
| customer_id | joined_on  | tenure_days |
+-------------+------------+-------------+
|           1 | 2025-11-03 |         239 |
|           2 | 2025-12-14 |         198 |
|           3 | 2026-01-09 |         172 |
|           4 | 2026-01-22 |         159 |
+-------------+------------+-------------+

SELECT date('2026-01-31', '+1 month') AS sqlite_plus_month;

+-------------------+
| sqlite_plus_month |
+-------------------+
| 2026-03-03        |
+-------------------+

-- MySQL
SELECT DATEDIFF('2026-06-30', '2026-01-12')         AS days_between,
       DATE_ADD('2026-01-31', INTERVAL 1 MONTH)     AS plus_month,
       '2026-06-02' + INTERVAL 7 DAY                AS plus_week,
       EXTRACT(MONTH FROM '2026-06-02')             AS month_no,
       DATE_FORMAT('2026-06-02', '%Y-%m')           AS ym;

+--------------+------------+------------+----------+---------+
| days_between | plus_month | plus_week  | month_no | ym      |
+--------------+------------+------------+----------+---------+
|          169 | 2026-02-28 | 2026-06-09 |        6 | 2026-06 |
+--------------+------------+------------+----------+---------+

-- PostgreSQL
SELECT DATE '2026-06-30' - DATE '2026-01-12'               AS days_between,
       DATE '2026-01-31' + INTERVAL '1 month'               AS plus_month,
       DATE '2026-06-02' + 7                                AS plus_week,
       EXTRACT(MONTH FROM DATE '2026-06-02')                AS month_no,
       TO_CHAR(DATE '2026-06-02', 'YYYY-MM')                AS ym;

+--------------+---------------------+------------+----------+---------+
| days_between | plus_month          | plus_week  | month_no | ym      |
+--------------+---------------------+------------+----------+---------+
|          169 | 2026-02-28 00:00:00 | 2026-06-09 |        6 | 2026-06 |
+--------------+---------------------+------------+----------+---------+

SELECT CAST('42' AS INTEGER) + 1     AS ok,
       CAST('12abc' AS INTEGER)      AS partly_number,
       CAST('abc' AS INTEGER)        AS not_number,
       CAST(3.99 AS INTEGER)         AS truncated,
       typeof(CAST(1200 AS REAL))    AS type_after;

+----+---------------+------------+-----------+------------+
| ok | partly_number | not_number | truncated | type_after |
+----+---------------+------------+-----------+------------+
| 43 |            12 |          0 |         3 | real       |
+----+---------------+------------+-----------+------------+

-- PostgreSQL
SELECT CAST('12abc' AS INTEGER);

Error: invalid input syntax for type integer: "12abc"

-- PostgreSQL
CREATE TABLE eval_runs (model TEXT PRIMARY KEY, correct INTEGER, attempted INTEGER);
INSERT INTO eval_runs VALUES ('small-v1', 41, 50), ('large-v2', 47, 50), ('new-v3', 0, 0);

SELECT model, correct * 100.0 / attempted AS accuracy_pct FROM eval_runs;

Error: division by zero

-- PostgreSQL
SELECT model,
       ROUND(correct * 100.0 / NULLIF(attempted, 0), 1) AS accuracy_pct
FROM eval_runs
ORDER BY model;

+----------+--------------+
| model    | accuracy_pct |
+----------+--------------+
| large-v2 |         94.0 |
| new-v3   | NULL         |
| small-v1 |         82.0 |
+----------+--------------+

from sqlhelp import con, show

def word_count(text):
    return None if text is None else len(text.split())

con.create_function("word_count", 1, word_count, deterministic=True)

show("""
    SELECT name, word_count(name) AS words, LENGTH(name) AS chars
    FROM products
    WHERE category_id = 1 OR product_id = 5
    ORDER BY product_id
""")

+---------------------------+-------+-------+
| name                      | words | chars |
+---------------------------+-------+-------+
| Python Crash Course       |     3 |    19 |
| Hands-On Machine Learning |     3 |    25 |
| 27-inch Monitor           |     2 |    15 |
+---------------------------+-------+-------+

-- সহজ
SELECT customer_id, UPPER(name) AS name, SUBSTR(email, 1, INSTR(email, '@') - 1) AS user
FROM customers
ORDER BY customer_id;

+-------------+-----------------+---------+
| customer_id | name            | user    |
+-------------+-----------------+---------+
|           1 | NADIA RAHMAN    | nadia   |
|           2 | TANVIR AHMED    | tanvir  |
|           3 | FARHANA AKTER   | farhana |
|           4 | RAFIQ ISLAM     | rafiq   |
|           5 | SADIA CHOWDHURY | sadia   |
|           6 | IMRAN HOSSAIN   | imran   |
|           7 | MITU DAS        | mitu    |
|           8 | KARIM UDDIN     | karim   |
+-------------+-----------------+---------+

-- মাঝারি
SELECT product_id, name, price,
       CAST(ROUND(price * 1.15) AS INTEGER) AS price_with_vat,
       ROUND(price / 1000.0, 1)             AS price_k
FROM products
WHERE price < 3000
ORDER BY price, product_id;

+------------+---------------------------+-------+----------------+---------+
| product_id | name                      | price | price_with_vat | price_k |
+------------+---------------------------+-------+----------------+---------+
|          4 | Wireless Mouse            |   900 |           1035 |     0.9 |
|         11 | Gift Card                 |  1000 |           1150 |     1.0 |
|          1 | Python Crash Course       |  1200 |           1380 |     1.2 |
|          8 | Laptop Stand              |  1500 |           1725 |     1.5 |
|          9 | USB-C Hub                 |  2200 |           2530 |     2.2 |
|          2 | Hands-On Machine Learning |  2500 |           2875 |     2.5 |
+------------+---------------------------+-------+----------------+---------+

-- কঠিন, অংশ ১: ফিচার সারি
SELECT customer_id,
       SUBSTR(name, 1, INSTR(name, ' ') - 1)                            AS first_name,
       SUBSTR(email, INSTR(email, '@') + 1)                             AS email_domain,
       COALESCE(city, 'Unknown')                                        AS city,
       strftime('%Y-%m', joined_on)                                     AS joined_month,
       CAST(julianday('2026-06-30') - julianday(joined_on) AS INTEGER)  AS tenure_days
FROM customers
ORDER BY customer_id;

+-------------+------------+--------------+------------+--------------+-------------+
| customer_id | first_name | email_domain | city       | joined_month | tenure_days |
+-------------+------------+--------------+------------+--------------+-------------+
|           1 | Nadia      | example.com  | Dhaka      | 2025-11      |         239 |
|           2 | Tanvir     | example.com  | Chattogram | 2025-12      |         198 |
|           3 | Farhana    | example.com  | Dhaka      | 2026-01      |         172 |
|           4 | Rafiq      | example.com  | Sylhet     | 2026-01      |         159 |
|           5 | Sadia      | example.com  | Unknown    | 2026-02      |         145 |
|           6 | Imran      | example.com  | Khulna     | 2026-02      |         132 |
|           7 | Mitu       | example.com  | Dhaka      | 2026-03      |          92 |
|           8 | Karim      | example.com  | Rajshahi   | 2026-04      |          80 |
+-------------+------------+--------------+------------+--------------+-------------+

# কঠিন, অংশ ২: SQL থেকে ব্যবহার করা একটা Python ফাংশন
from sqlhelp import con, show

def mask_email(email):
    if email is None or "@" not in email:
        return email
    user, domain = email.split("@", 1)
    return user[0] + "***@" + domain

con.create_function("mask_email", 1, mask_email, deterministic=True)
show("SELECT customer_id, mask_email(email) AS email FROM customers WHERE customer_id <= 3 ORDER BY customer_id")

+-------------+------------------+
| customer_id | email            |
+-------------+------------------+
|           1 | n***@example.com |
|           2 | t***@example.com |
|           3 | f***@example.com |
+-------------+------------------+