home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

แยกวันที่จากข้อความใน MySQL ด้วย SUBSTRING_INDEX

สถานการณ์

ข้อมูลวันที่เก็บรวมอยู่ในข้อความ (String) เช่น 'John has got joining date.12/31/2018' ต้องการดึงเฉพาะส่วนวันที่ออกมา

วิธีแก้: ใช้ SUBSTRING_INDEX() ซ้อนกัน

SUBSTRING_INDEX(str, delim, count) คืนค่าส่วนของสตริงก่อน/หลัง Delimiter ครั้งที่ count

ตัวอย่างการใช้งาน

1. สร้างตาราง

mysql> CREATE TABLE DemoTable (
    -> Title TEXT
    -> );
Query OK, 0 rows affected (0.58 sec)

2. เพิ่มข้อมูล

mysql> INSERT INTO DemoTable VALUES('John has got joining date.12/31/2018');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO DemoTable VALUES('Carol has got joining date.01/11/2019');
Query OK, 1 row affected (0.22 sec)
mysql> INSERT INTO DemoTable VALUES('Sam will arrive at.12/03/2050');
Query OK, 1 row affected (0.87 sec)

3. แสดงข้อมูลต้นฉบับ

mysql> SELECT * FROM DemoTable;

ผลลัพธ์:

+---------------------------------------+
| Title                                 |
+---------------------------------------+
| John has got joining date.12/31/2018  |
| Carol has got joining date.01/11/2019 |
| Sam will arrive at.12/03/2050         |
+---------------------------------------+
3 rows in set (0.00 sec)

4. แยกวันที่ (ส่วนหลังจุดสุดท้าย)

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Title, '.', -1), '.', 1) FROM DemoTable;

ผลลัพธ์:

+----------------------------------------------------------+
| substring_index(substring_index(Title,".", -1), ".", 1)  |
+----------------------------------------------------------+
| 12/31/2018                                               |
| 01/11/2019                                               |
| 12/03/2050                                               |
+----------------------------------------------------------+
3 rows in set (0.00 sec)

อธิบาย Logic

  1. SUBSTRING_INDEX(Title, '.', -1) → หา Delimiter '.' ตัวสุดท้ายจากขวา คืนค่าส่วนหลัง: '12/31/2018', '01/11/2019', '12/03/2050'
  2. SUBSTRING_INDEX(..., '.', 1) → หา '.' ตัวแรกจากซ้ายของผลลัพธ์ข้างต้น (ไม่มี) คืนค่าทั้งสตริง

วิธีอื่นๆ ในการแยกวันที่

1. ใช้ SUBSTRING + LOCATE (ถ้ารูปแบบคงที่)

-- สมมติวันที่อยู่ท้าย 10 ตัวอักษร
SELECT SUBSTRING(Title, -10) FROM DemoTable;

2. ใช้ REGEXP (MySQL 8.0+)

SELECT REGEXP_SUBSTR(Title, '[0-9]{2}/[0-9]{2}/[0-9]{4}') FROM DemoTable;

3. ใช้ STR_TO_DATE แปลงเป็น DATE Type

SELECT STR_TO_DATE(SUBSTRING_INDEX(Title, '.', -1), '%m/%d/%Y') AS JoinDate
FROM DemoTable;

Best Practice

  • หลีกเลี่ยงเก็บวันที่ใน Text Column → ใช้ DATE/DATETIME Type
  • หากต้องเก็บข้อความผสม ใช้ JSON หรือแยกคอลัมน์
  • สร้าง Generated Column (MySQL 5.7+) เพื่อแยกวันที่อัตโนมัติ
  • Index บน Generated Column เพื่อ Query เร็วขึ้น

Generated Column Example (MySQL 5.7+)

ALTER TABLE DemoTable
ADD COLUMN JoinDate DATE 
GENERATED ALWAYS AS (STR_TO_DATE(SUBSTRING_INDEX(Title, '.', -1), '%m/%d/%Y')) STORED;