SQLite ฟังก์ชันสตริง: REPLACE, SUBSTR, TRIM, ROUND

โดย: มาร์คัสอัลเลน มาร์คัสอัลเลน
Hours วันที่อัพเดท

SQLite โดยค่าเริ่มต้นจะมีรายการฟังก์ชันในตัวอยู่ภายใน SQLite ห้องสมุด. คุณยังสามารถเพิ่มฟังก์ชันแบบกำหนดเองอื่นๆ โดยใช้ภาษา C เพื่อขยายฟังก์ชันหลักได้

โปรดทราบว่าสำหรับตัวอย่างทั้งหมดต่อไปนี้ คุณต้องเรียกใช้ sqlite3.exe และเปิดการเชื่อมต่อกับฐานข้อมูลตัวอย่างแบบไหล:

ขั้นตอน 1) ในขั้นตอนนี้

  1. เปิด My Computer และไปที่ไดเร็กทอรีต่อไปนี้ “C:\sqlite” แล้ว
  2. เปิด "sqlite3.exe"

SQLite ฟังก์ชันสตริง

ขั้นตอน 2) เปิดฐานข้อมูล “บทช่วยสอนSampleDB.db” โดยคำสั่งต่อไปนี้:

SQLite ฟังก์ชันสตริง

ตอนนี้คุณพร้อมที่จะเรียกใช้แล้ว การสอบถาม และลองทดสอบคำถามต่างๆ ที่ใช้ในหัวข้อต่อไปนี้

ค้นหา LENGTH ของสตริงใน SQLite

หากต้องการค้นหาความยาวของสตริงให้ใช้ LENGTH(X) โดยที่ X คือค่าสตริง ถ้า X เป็นค่า Null ฟังก์ชันความยาวจะส่งกลับค่า Null

คุณยังสามารถใช้ฟังก์ชันความยาวกับค่าตัวเลขเพื่อรับความยาวของค่าตัวเลขได้

ตัวอย่าง:

ในตัวอย่างต่อไปนี้ เราจะลองใช้รูปแบบฟังก์ชัน LENGTH ด้วยค่าดังต่อไปนี้:

SELECT LENGTH('A string'), LENGTH(NULL), LENGTH(20), LENGTH(20.5);

สิ่งนี้จะทำให้คุณ:

ค้นหา LENGTH ของสตริงใน SQLite

ผลลัพธ์เป็นดังนี้:

  • LENGTH('A string') คืนค่า 8 ซึ่งเป็นความยาวของสตริง “A string”
  • LENGTH(NULL) ส่งคืนค่าว่าง
  • LENGTH(20) ส่งคืน 2 เนื่องจาก 20 มีความยาว 2 ความยาว
  • LENGTH(20.5) คืนค่า 4; จุดทศนิยมลอยตัว “.” นับเป็นอักขระ ดังนั้นคุณจะมีอักขระ 3 ตัว – อักขระ XNUMX ตัวซึ่งเป็นความยาวของตัวเลข บวกอักขระอีกหนึ่งตัวสำหรับจุดทศนิยมลอยตัว “.”

การเปลี่ยนเคสด้วยฟังก์ชัน UPPER และฟังก์ชัน LOWER

UPPER(X) จะส่งกลับสตริง x เดียวกัน แต่จะแสดงอักขระสตริงทั้งหมดในอักขระตัวพิมพ์ใหญ่

LOWER(X) จะส่งกลับสตริง x เดียวกัน แต่จะแสดงอักขระสตริงทั้งหมดเป็นอักขระขนาดเล็ก

UPPER และ LOWER จะส่งกลับค่า Null หากคุณส่งค่า Null ไปให้

หากคุณส่งค่าตัวเลขไปที่ UPPER หรือ LOWER ทั้งสองค่าจะส่งกลับค่าตัวเลขที่ตรงกันทุกประการ

ตัวอย่าง:

SELECT UPPER('a string'), LOWER('A STRING'), UPPER(20), LOWER(20), UPPER(NULL), LOWER(NULL);

สิ่งนี้จะทำให้คุณ:

ตัวอย่างฟังก์ชัน UPPER และ LOWER ใน SQLite

ผลลัพธ์เป็นดังนี้:

  • UPPER('a string') ส่งกลับสตริง “สตริง” ในรูปแบบอักษรตัวใหญ่ “สตริง"
  • LOWER('A STRING') ส่งกลับสตริง “สตริง” ในรูปแบบอักษรตัวเล็ก “สตริง"
  • UPPER(20), LOWER(20) คืนค่าตัวเลขเดียวกัน เนื่องจากไม่มีผลใดๆ ต่อตัวเลข
  • UPPER(NULL), LOWER(NULL) คืนค่า null เนื่องจากเราส่งค่า null ไปให้

บทความที่เกี่ยวข้อง

SQLite ฟังก์ชันย่อย

ฟังก์ชัน SUBSTR คืนค่าสตริงจำนวนหนึ่งโดยเริ่มจากตำแหน่งที่ระบุ คุณสามารถส่งตัวดำเนินการสามตัวไปยังฟังก์ชันได้ เช่นนี้ซับสเตรต(X,Y,Z)” ดังต่อไปนี้:

  • X คือสตริงลิเทอรัลหรือคอลัมน์สตริงที่จะแยกวิเคราะห์ คุณสามารถส่งค่าตามตัวอักษร (ค่าคงที่) หรือชื่อคอลัมน์ได้ และในกรณีนี้ ค่าจะถูกอ่านจากค่าของคอลัมน์
  • Y คือตำแหน่งเริ่มต้นที่จะลบสตริงที่เริ่มต้นจากนั้น
  • Z คือจำนวนอักขระที่จะแยกวิเคราะห์โดยเริ่มจากตำแหน่งเฉพาะ Y หมายเลขนี้เป็นทางเลือก คุณสามารถละเว้นได้ และในกรณีนี้ SQLite จะลบสตริงที่เริ่มต้นจากตำแหน่ง Y บอกจุดสิ้นสุดของสตริง

ตัวอย่าง:

ในการสอบถามต่อไปนี้ เราจะใช้ฟังก์ชัน SUBSTR เพื่อรับอักขระ 4 ตัวโดยเริ่มจากอักขระตัวที่สองจากชื่อของนักเรียน:

SELECT StudentName, SUBSTR(StudentName, 2, 4), SUBSTR(StudentName, 2)
FROM Students;

สิ่งนี้จะทำให้คุณ:

ตัวอย่างฟังก์ชัน SUBSTR ใน SQLite

  • SUBSTR(ชื่อนักเรียน, 2, 4) ซับสตริง “ชื่อนักเรียน” โดยเริ่มจากอักขระตัวที่สอง และส่งคืนอักขระ 4 ตัวถัดไป
  • อย่างไรก็ตามในการปฏิบัติหน้าที่ SUBSTR(ชื่อนักเรียน, 2) เราไม่ได้ระบุตัวเลขสำหรับอักขระที่จะส่งคืน นั่นเป็นสาเหตุ SQLite ส่งคืนอักขระที่เหลือทั้งหมดใน "ชื่อนักเรียน” เริ่มจากตำแหน่งที่สอง

การเปลี่ยนส่วนของ String ด้วย SQLite แทนที่ฟังก์ชัน

REPLACE ใช้เพื่อแทนที่แต่ละสตริงที่เกิดขึ้นกับสตริงอื่นด้วยสตริง

REPLACE(X, Y, Z) – X คือสตริงอินพุตตามตัวอักษรหรือคอลัมน์ Y คือสตริงที่จะแทนที่ด้วยสตริง Z โปรดทราบว่าสตริงนั้นจะแทนที่สตริง Y ใดๆ ด้วย Z ไม่ใช่แค่สตริงเดียว

ตัวอย่าง:

ในแบบสอบถามต่อไปนี้ เราจะแทนที่การเกิดขึ้นทั้งหมดของสตริง “xx” ด้วยสตริง “SQLite"

SELECT REPLACE('xx is very lightweight, xx is easy to learn', 'xx', 'SQLite');

สิ่งนี้จะทำให้คุณ

ตัวอย่างฟังก์ชัน REPLACE ใน SQLite

  • ฟังก์ชั่นแทนที่แทนที่สตริงทั้งหมด “xx” ด้วยสตริง “SQLite"
  • ดังนั้นผลลัพธ์ที่ได้จะเป็น "SQLite มีน้ำหนักเบามาก SQLite ง่ายต่อการเรียนรู้”

ตัดแต่งช่องว่างด้วย SQLite ฟังก์ชัน TRIM

TRIM ลบช่องว่างออกจากจุดเริ่มต้นหรือจุดสิ้นสุดของสตริง จะไม่ลบช่องว่างใดๆ ตรงกลางสตริง เฉพาะจากจุดเริ่มต้นหรือจุดสิ้นสุดเท่านั้น

ตัวอย่าง:

ในการสอบถามต่อไปนี้ เราจะใช้ฟังก์ชัน TRIM เพื่อลบช่องว่างจากจุดเริ่มต้นและจุดสิ้นสุดของสตริง

โปรดทราบว่าตัวดำเนินการเรียงต่อกัน “||” จะใช้เพื่อเพิ่ม “;” พิเศษลงท้ายสตริง เพื่อแสดงให้คุณเห็นว่าช่องว่างในหน้าต่างสีดำถูกลบออกไปแล้ว

SELECT TRIM('      SQLite is easy to learn        ') || ';';

สิ่งนี้จะทำให้คุณ:

ตัวอย่างฟังก์ชัน TRIM ใน SQLite

  • สังเกตว่าช่องว่างถูกลบออกจากจุดเริ่มต้นและจุดสิ้นสุดของสตริง แทนที่ด้วย ' ; 'ในตอนท้าย.

การอ่านค่าสัมบูรณ์ด้วย SQLite ฟังก์ชัน ABS

ฟังก์ชัน ABS คืนค่าสัมบูรณ์ของค่าตัวเลข ค่าสัมบูรณ์คือค่าตัวเลขที่ไม่มีเครื่องหมายบวกหรือลบ ABS(X) คืนค่าที่ขึ้นอยู่กับค่าของ X ดังต่อไปนี้:

  • ค่าสัมบูรณ์ของ X ถ้า X เป็นค่าตัวเลข
  • ค่า NULL ถ้า X เป็นค่า null
  • ค่า "0.0" หาก X เป็นสตริง

ตัวอย่าง:

ในแบบสอบถามต่อไปนี้ เราจะพยายามรับค่าสัมบูรณ์ของสตริง ตัวเลข และค่า null โดยใช้ฟังก์ชัน ABS:

SELECT ABS(-2), ABS(+2), ABS('a string'), ABS(null);

สิ่งนี้จะทำให้คุณ:

ตัวอย่างฟังก์ชัน ABS ใน SQLite

ผลลัพธ์เป็นดังนี้:

  • ABS(-2), ABS(+2) คืนค่า 2 เนื่องจาก 2 เป็นค่าสัมบูรณ์สำหรับทั้ง "-2" และ "+2"
  • ABS('a string') ส่งคืนค่า "0.0" เนื่องจากคุณส่งค่าสตริงไม่ใช่ค่าตัวเลข
  • ABS(null) ส่งกลับค่า null เนื่องจากคุณส่งผ่านค่า null

การปัดเศษค่าด้วยฟังก์ชัน ROUND ใน SQLite

ตัวเลขทศนิยมคือตัวเลขที่มีจุดลอยตัว “.” อยู่ เช่น “20.5”, “8.65” ส่วนที่อยู่ทางซ้ายของจุดลอยตัวเรียกว่าส่วนจำนวนเต็ม และส่วนที่อยู่ทางขวาของจุดลอยตัวเรียกว่าส่วนทศนิยม

ตัวเลขจำนวนเต็มคือตัวเลขที่ไม่มีจุดลอยตัว ตัวอย่างเช่น "20", "8"

ROUND(X) จะแปลงค่าทศนิยมหรือคอลัมน์ X ให้เป็นจำนวนเต็มเท่านั้น ส่วนจำนวนเต็มที่เป็นส่วนที่อยู่ทางซ้ายของจุดทศนิยมจะถูกส่งคืน และตัวเลขทั้งหมดที่อยู่ทางขวาของจุดทศนิยมจะถูกลบออก

ตัวอย่าง:

ในแบบสอบถามต่อไปนี้ เราจะลองใช้ฟังก์ชัน ROUND ด้วยตัวเลือกต่างๆ:

SELECT ROUND(12.4354354), ROUND(12.4354354, 2), ROUND(NULL), ROUND('a string');

สิ่งนี้จะทำให้คุณ:

ตัวอย่างฟังก์ชัน ROUND ใน SQLite

ผลลัพธ์เป็นดังนี้:

  • ROUND(12.4354354) – ส่งกลับค่า “12” เนื่องจากคุณไม่ได้ระบุจำนวนหลัก ดังนั้น, SQLite ลบส่วนทศนิยมทั้งหมดออก
  • ROUND(12.4354354, 2) – ส่งกลับค่า “12.44” เนื่องจากคุณระบุตัวเลขเพียง 2 หลักที่จะปัดเศษ ซึ่งเป็นตัวเลขสองหลักจากด้านขวาของจุดทศนิยม
  • ROUND(NULL) – ส่งกลับค่า null เนื่องจากคุณส่งค่า null ไปให้
  • ROUND('a string') – ส่งกลับค่า “0.0” เนื่องจากคุณส่งค่าสตริงไปให้

การค้นหาชนิดข้อมูลของนิพจน์ด้วยฟังก์ชัน TYPEOF

หากคุณต้องการค้นหาประเภทของคอลัมน์หรือค่าตัวอักษร คุณสามารถใช้ฟังก์ชัน TYPEOF เพื่อดำเนินการดังกล่าวได้

ฟังก์ชัน TYPEOF(X) จากชื่อจะส่งกลับชนิดข้อมูลของนิพจน์ X โดยจะส่งกลับค่าสตริงที่ระบุประเภทข้อมูลของ X โดยจะส่งกลับค่าตามประเภทข้อมูล ไม่ว่าจะเป็นค่า “NULL” จริง ข้อความ จำนวนเต็ม … ฯลฯ

ตัวอย่าง:

ในแบบสอบถามต่อไปนี้ เราจะลองใช้ฟังก์ชัน TYPEOF กับค่าตัวอักษรประเภทต่างๆ:

SELECT TYPEOF(null), TYPEOF(12), TYPEOF(12.5), TYPEOF('a string');

สิ่งนี้จะทำให้คุณ:

ตัวอย่างฟังก์ชัน TYPEOF ใน SQLite

ผลลัพธ์ที่ได้กลับมาเป็นดังต่อไปนี้:

  • TYPEOF(null) – ส่งกลับค่า null เนื่องจากคุณส่งค่า null ไปให้
  • TYPEOF(12) – ส่งกลับจำนวนเต็มเนื่องจาก 12 เป็นตัวเลขจำนวนเต็ม
  • TYPEOF(12.5) – ส่งคืนค่า REAL เนื่องจาก 12.5 เป็นจำนวนจริง
  • TYPEOF('a string') – ส่งกลับข้อความเป็น “a string” เป็นข้อความ

ค้นหาบันทึกที่แทรกล่าสุดด้วย SQLite LAST_INSERT_ROWID

SQLite กำหนดคีย์จำนวนเต็ม (Id) ให้กับแถวทั้งหมดในตารางทั้งหมด หมายเลขนี้ใช้เพื่อระบุแถวเหล่านี้โดยไม่ซ้ำกัน

เมื่อคุณแทรกแถวใหม่บนโต๊ะ SQLite จะกำหนดค่าที่ไม่ซ้ำกันให้กับแถว

หากตารางมีคีย์หลักที่ประกาศในคอลัมน์เดียวเท่านั้น และคอลัมน์นั้นเป็นประเภทข้อมูล INTEGER ค่าของคอลัมน์นี้จะถูกใช้เป็นแถว

ฟังก์ชัน LAST_INSERT_ROWID() จะส่งคืน ROW_ID ของแถวสุดท้ายที่แทรกในตารางใดๆ ในฐานข้อมูล โปรดทราบว่าฟังก์ชันนี้จะไม่รับตัวดำเนินการใดๆ

ตัวอย่าง:

ในตัวอย่างต่อไปนี้เราจะรันฟังก์ชัน LAST_INSERT_ROWID() ดังต่อไปนี้:

SELECT LAST_INSERT_ROWID();

สิ่งนี้จะทำให้คุณ:

ตัวอย่างของฟังก์ชัน LAST_INSERT_ROWID ใน SQLite

  • LAST_INSERT_ROWID() ส่งคืนค่า 0 เนื่องจากไม่มีแถวแทรกลงในตารางใดๆ ในการเชื่อมต่อฐานข้อมูลปัจจุบัน

ตอนนี้ เรามาแทรกนักเรียนใหม่และรันฟังก์ชัน LAST_INSERT_ROWID() อีกครั้งหลังจากคำสั่ง insert เช่นนี้:

INSERT INTO Students VALUES(11, 'guru', 1, '1998-10-12');
SELECT LAST_INSERT_ROWID();

สิ่งนี้จะทำให้คุณ:

ตัวอย่างของฟังก์ชัน LAST_INSERT_ROWID ใน SQLite

  • หลังจากที่เราแทรกนักเรียนใหม่ด้วยรหัส 11 แล้ว LAST_INSERT_ROWID() จะส่งคืนรหัสแถวที่แทรกล่าสุดซึ่งก็คือ 11

รับเวอร์ชันของคุณ SQLite ห้องสมุด

เพื่อให้ได้เวอร์ชั่นของคุณ SQLite ในไลบรารี คุณต้องเรียกใช้ฟังก์ชัน SQLITE_VERSION()

ตัวอย่าง:

มาดูกันว่าเป็นรุ่นอะไร SQLite เรากำลังใช้โดยการรันคำสั่งต่อไปนี้:

SELECT SQLITE_VERSION();

สิ่งนี้จะทำให้คุณ:

ตัวอย่างการรับเวอร์ชันของคุณ SQLite ห้องสมุด

SQLITE_VERSION() ส่งคืน 3.9.2 ซึ่งเป็นเวอร์ชันของ SQLite เรากำลังใช้อยู่

การสร้างฟังก์ชันที่ผู้ใช้กำหนดและฟังก์ชันรวมที่ผู้ใช้กำหนด

สำหรับผู้ใช้บางราย SQLite ฟังก์ชันหลักมีจำกัด พวกเขาอาจจำเป็นต้องเพิ่มฟังก์ชันแบบกำหนดเองตามความต้องการแบบกำหนดเอง

SQLite ไม่รองรับการสร้างฟังก์ชันที่ผู้ใช้กำหนด ไม่เหมือนกับระบบการจัดการฐานข้อมูลอื่นๆ คุณไม่สามารถสร้างฟังก์ชันที่ผู้ใช้กำหนดได้ SQLite โดยตรง.

อย่างไรก็ตาม คุณสามารถสร้างฟังก์ชันโดยใช้ภาษาการเขียนโปรแกรม เช่น C#, C, PHP หรือ C++ และแนบฟังก์ชันนั้นเข้ากับแกนกลาง SQLite ทำหน้าที่ใน SQLite ห้องสมุดเองโดยใช้ “sqlite3_สร้างฟังก์ชัน" การทำงาน. จากนั้นคุณสามารถนำมาใช้ซ้ำในฐานข้อมูลของคุณได้

ตัวอย่าง:

ในตัวอย่างต่อไปนี้ เราจะสร้างฟังก์ชันที่ผู้ใช้กำหนดโดยใช้ C# ภาษาโปรแกรมและเพิ่มเข้าไป SQLite ฟังก์ชั่น:

[SQLiteFunction(Name = "DoubleValue", Arguments = 1, FuncType = FunctionType.Scalar)]
public class DoubleValue: SQLiteFunction
{
    public override object Invoke(object[] args)
    {
        return args[0] * 2;
    }
}

ข้อมูลโค้ดนี้เขียนอยู่ในไฟล์ ภาษาการเขียนโปรแกรม C#- มันสร้างฟังก์ชัน C# โดยใช้รหัส C#

ชื่อฟังก์ชันจะเป็น “Doubleความคุ้มค่า” ใช้พารามิเตอร์ตัวเดียวและส่งคืนค่าคูณด้วย 2

โปรดทราบว่า C# จะเพิ่มฟังก์ชันนี้โดยอัตโนมัติ SQLite- สิ่งที่คุณต้องมีคือคอมไพล์และรันโค้ดนี้ จากนั้น C# จะเพิ่มฟังก์ชันนั้นที่มีชื่อเดียวกันลงในรายการ SQLite ฟังก์ชั่น. จากนั้นคุณสามารถใช้ฟังก์ชันนี้ได้ใน SQLite.

เช่นเดียวกับการใช้ภาษาโปรแกรมเช่น C#, C, PHP หรือ C++คุณยังสามารถสร้างฟังก์ชันรวมที่ผู้ใช้กำหนดได้ ซึ่งใช้เพื่อขยายฟังก์ชันการรวมเข้า SQLite และใช้มันเพื่อสร้างฟังก์ชันการรวมแบบกำหนดเอง

สรุป

  • SQLite มีชุดฟังก์ชันในตัวที่ครอบคลุมซึ่งทำให้การทำงานกับตารางและคอลัมน์ของฐานข้อมูลเป็นเรื่องง่ายอย่างที่เราเห็น คุณสามารถใช้ฟังก์ชันเหล่านี้กับคอลัมน์และค่าตามตัวอักษรภายในนิพจน์ใดๆ ในการสืบค้น SQL ของคุณได้
  • คุณยังสามารถใช้ฟังก์ชันความยาวกับค่าตัวเลขเพื่อรับความยาวของค่าตัวเลขได้
  • UPPER และ LOWER จะส่งกลับค่า Null หากคุณส่งค่า Null ไปให้
  • ฟังก์ชัน SUBSTR ส่งกลับสตริงตามจำนวนที่ระบุ โดยเริ่มจากตำแหน่งที่ระบุ
  • REPLACE ใช้เพื่อแทนที่แต่ละสตริงที่เกิดขึ้นกับสตริงอื่นด้วยสตริง
  • TRIM จะลบช่องว่างออกจากจุดเริ่มต้นหรือจุดสิ้นสุดของสตริง
  • ฟังก์ชัน ABS ส่งกลับค่าสัมบูรณ์ของค่าตัวเลข
  • ROUND(X) แปลงค่าทศนิยมหรือคอลัมน์ X ให้เป็นเลขจำนวนเต็มเท่านั้น
  • ฟังก์ชัน TYPEOF(X) จะส่งกลับชนิดข้อมูลของนิพจน์ X จากชื่อ
  • ฟังก์ชัน LAST_INSERT_ROWID() จะส่งคืน ROW_ID ของแถวที่แทรกล่าสุดในตารางใดๆ ในฐานข้อมูล