SQLite相關核心函數
SQLite相關核心函數
PS.關於字串處理有[instr(搜尋)、length(長度)、replace(替換/取代)、rtrim(刪除右側)、substr(子字串)、trim(刪除)、ltrim(刪除左側)]
abs()~[絕對值]-http://www.w3resource.com/sqlite/core-functions-abs.php
sqlite> SELECT abs(5);
Here is the result.
abs(5)
———-
5
changes()~[被修改筆數]-http://www.w3resource.com/sqlite/core-functions-changes.php
sqlite> INSERT INTO table1 VALUES(1, ‘AAA’);
Here is the result.
sqlite> SELECT changes();
changes()
———-
1
char()~[數字轉字元]-http://www.w3resource.com/sqlite/core-functions-char.php
sqlite>SELECT char(67,72,65,82);
———-
CHAR
coalesce()~[拷貝第一個非空的值]-http://www.w3resource.com/sqlite/core-functions-coalesce.php
sqlite> SELECT coalesce(NULL, 2, 3);
coalesce(NULL, 2, 3)
——————–
2
glob()~[]-http://www.w3resource.com/sqlite/core-functions-glob.php
SELECT glob(67,72);
glob(67,72)
———–
0
ifnull()~[非空的值]-http://www.w3resource.com/sqlite/core-functions-ifnull.php
SELECT ifnull(0,2);
ifnull(0,2)
———–
0
ifnull(NULL,2)
————–
2
instr()~[找出子字串的起始位置]-http://www.w3resource.com/sqlite/core-functions-instr.php
SELECT instr(‘myteststring’,’st’);
instr(‘myteststring’,’st’)
————————–
5
hex()~[ASCII->16進位]-http://www.w3resource.com/sqlite/core-functions-hex.php
SELECT hex(67);
hex(67)
———-
3637
last_insert_rowid()~[查詢最後一筆的新增編號]-http://www.w3resource.com/sqlite/core-functions-last_insert_rowid.php
SELECT last_insert_rowid();
last_insert_rowid()
——————-
1
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
——————-
2
length()~[計算字串長度]-http://www.w3resource.com/sqlite/core-functions-length.php
SELECT pub_id,LENGTH(pub_name) FROM publisher;
pub_id LENGTH(pub_name)
———- —————-
P001 19
P002 15
P003 23
P004 16
P005 20
P006 24
P007 28
P008 20
like()~[字串模糊比對]-http://www.w3resource.com/sqlite/core-functions-like.php
lower()~[字串轉小寫]-http://www.w3resource.com/sqlite/core-functions-lower.php
ltrim()~[刪除字串左側字元(沒有指定=空白)]-http://www.w3resource.com/sqlite/core-functions-ltrim.php
SELECT ‘ Hello’as “Original String”, ltrim(‘ Hello’);
Original String ltrim(‘ Hello’)
————— —————
Hello Hello
SELECT ‘disqualify’,ltrim(‘disqualify’,’dis’);
‘disqualify’ ltrim(‘disqualify’,’dis’)
———— ————————
disqualify qualify
nullif()~[函數返回它的參數的第一個參數是不同的，如果參數是相同的，則返回NULL]-http://www.w3resource.com/sqlite/core-functions-nullif.php
quote()~[回傳一個字串]-http://www.w3resource.com/sqlite/core-functions-quote.php
random()~[亂數整數-9223372036854775808 and +9223372036854775807]-http://www.w3resource.com/sqlite/core-functions-random.php
replace()~[字串搜尋(尋找)替換(交換/取代)]-http://www.w3resource.com/sqlite/core-functions-replace.php
SELECT employee_id,job_id, replace(job_id,’ST’,’VT’) FROM job_history WHERE department_id<100;
employee_id job_id replace(job_id,’ST’,’VT’)
———– ———- ————————-
102 IT_PROG IT_PROG
201 MK_REP MK_REP
114 ST_CLERK VT_CLERK
122 ST_CLERK VT_CLERK
200 AD_ASST AD_ASVT
176 SA_REP SA_REP
176 SA_MAN SA_MAN
200 AC_ACCOUNT AC_ACCOUNT
round()~[四捨五入]-http://www.w3resource.com/sqlite/core-functions-round.php
SELECT round(59.9,0);
Here is the result.
round(59.9,0)
————-
60.0
SELECT round(-4.535,2);
Here is the result.
round(-4.535,2)
————–
-4.54
rtrim()~[刪除字串右側字元(沒有指定=空白)]-http://www.w3resource.com/sqlite/core-functions-rtrim.php
select rtrim(‘stringtest’,’test’);
Here is the result.
rtrim(‘stringtest’,’test’)
————————–
string
substr()~[取出子字串]-http://www.w3resource.com/sqlite/core-functions-substr.php
SELECT substr(‘w3resource’,4,3);
Here is the output.
SUBSTR(‘w3resource’,4,3)
————————
eso
SELECT first_name, substr(first_name,3) FROM employees WHERE department_id=100;
Here is the result.
first_name substr(first_name,3)
———- ——————–
Nancy ncy
Daniel niel
John hn
Ismael mael
Jose Manue se Manuel
Luis is
trim()~[刪除字串內特定字元(沒有指定=空白)]-http://www.w3resource.com/sqlite/core-functions-trim.php
SELECT ‘madam’,trim(‘madam’,’ma’);
Here is the result.
‘madam’ trim(‘madam’,’ma’)
———- ——————
madam d
typeof()~[回傳資料型態]-http://www.w3resource.com/sqlite/core-functions-typeof.php
upper()~[字串轉大寫]-http://www.w3resource.com/sqlite/core-functions-upper.php