PostgreSQL常用字符串函數(shù)與示例說明小結(jié)
更新時間：2024年11月21日 14:51:26 作者：trayvontang
文章介紹了PostgreSQL中常用字符串函數(shù)的使用方法,包括空值處理、字符串位置查詢、長度計算、大小寫轉(zhuǎn)換、去除空格、連接、替換、匹配、拆分和截取等操作,感興趣的朋友跟隨小編一起看看吧
coalesce
coalesce主要用來處理空，它返回第1個不為空的值，可以接受整型或者字符串，但是不能混有。
select coalesce('a','0') as r1,coalesce(1,2) as r2,coalesce(null,2,1) as r3,coalesce(null,NULL,'first') as r4;
字符串位置(position strpos)
返回子字符串在字符串中的位置,有點像Java中的index
select position('456' in '123456789') as r1,strpos('123456789', '678') as r2;
字符串長度與大小寫轉(zhuǎn)換
select length('hello world') as len, upper('Hello World') as up, lower('Hello World') as lo;
去掉空格(trim ltrim rtrim)
select trim(' Hello World ') as t,
rtrim(' Hello World ') as r,
ltrim(' Hello World ') as l;
字符串連接(concat)
select concat('Hello', ' ', 'World');
select concat(ip, ' ', port) as id from user;PostgreSQL從9.1開始提供了||操作符，可以用來代替concat函數(shù)。
select ip || ' ' || port as id from user;
字符串替換
簡單替換(replace)
-- 第1個參數(shù)是源字符串，第2個參數(shù)是需要的替換的old，第3個參數(shù)是替換之后的new
select replace('啊哈, 娘子', '娘子', '相公') as r;
替換指定位置長度(overlay)
overlay(string placing substring from start [for length]): 用另一個字符串替換字符串的一部分。
-- 結(jié)果：123中文6干嘛orld
-- 將第7位開始的2位替換為了干嘛，其他不變
select overlay('123中文67world' placing '干嘛' from 7);
-- 結(jié)果:123中文6干嘛rld
-- 將第7位開始的3位替換為了干嘛，其他不變
select overlay('123中文67world' placing '干嘛' from 7 for 3);
正則替換(regexp_replace)
regexp_replace(string, pattern, replacement [, flags ]): 使用正則表達式替換字符串中的子字符串。
select regexp_replace('Hello123 World456', '\d+', '替換之后的內(nèi)容', 'g') as r;
字符串匹配
regexp_matches(string, pattern [, flags ]): 使用正則表達式匹配字符串中的子字符串。
-- \b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b
-- 返回第1個匹配數(shù)組
select regexp_matches('123c929 33哈哈123 33hh123','(\d+)[a-z]+(\d+)') as r;
-- 返回全部匹配數(shù)組
select regexp_matches('123c929 33哈哈123 33hh123','(\d+)[a-z]+(\d+)','g') as r;
字符串拆分
split_part(拆分數(shù)組取指定位置的值)
select split_part('1-2-3', '-', 2) as r;
string_to_array(拆分為數(shù)組)
select
string_to_array('1,2,3', ',') as r1,
string_to_array('1,2,3', ',', '2') as r2;
regexp_split_to_array(拆分為數(shù)據(jù)，使用正則表達式)
除了使用固定字符拆分，還可以使用正則表達式拆分：
select regexp_split_to_array('1ab22cd3ef4', '[a-z]+') as result;
regexp_split_to_table(拆分為表，多行)
除了拆分為數(shù)組，還可以拆分為table
SELECT
regexp_split_to_table('1,2,3,4,5,6', ',' ) as r1,
regexp_split_to_table('1。2。3。4。5。6', '。' ) as r2,
regexp_split_to_table('1|2|3|4|5|6', E'\\|') as r3,
regexp_split_to_table('1 2 3', ' ') as r4;
字符串取子串
- substring(content,start,length):第1個參數(shù)是要截取的子串，第2個參數(shù)是開始位置，從1開始，第3個參數(shù)是截取長度(可選,默認取到最后1個)
- substring(content from start for length):上1個的單參數(shù)模式
- substring(content,pattern):可以使用正則表達式
基本用法
start從1開始，如果小于1，自動修正為1，length如果大于最大長度，自動修正為最大長度。
select substring('Hello World',1,6) as S1L6,
substring('Hello World',1) as S1,
substring('Hello World',0) as S0,
substring('Hello World',0,20) as S0L20;
substring還有一個等價的函數(shù)substr：
select substr('Hello World',1,6) as S1L6,
substr('Hello World',1) as S1,
substr('Hello World',0) as S0,
substr('Hello World',0,20) as S0L20;單參數(shù)
select substring('Hello World' from 1 for 6) as S1L6,
substring('Hello World' from 1) as S1,
substring('Hello World' from 0) as S0,
substring('Hello World' from 0 for 20) as S0L20;
-- 截取時間就非常方便了
select substring('2050-01-01 12:00:00' from 1 for 10);
正則截取
還可以使用正則截?。?/p>
-- 截取第1個匹配
select substring('hello world c909 what the world c919 hi c929','([0-9]{1,4})') as r;
下面這個匹配哪一個？
select substring('what theA1234567890the ok hahahaA987654321','.*(A\d{5}).*') AS r;答案是：A98765,因為第1個.*是貪婪模式。
截取特定子串：
-- 截取從are開始的串
select substring('what are world',position('are' in 'what are world')) as r;left與right(左右截取)
-- 按字符長度，不是字節(jié)
select left('你好，hi,in the world',5) as r1,
right('你好，hi,in the world',5);
正則
元字符
- |：表示選擇兩個候選項之一
- *：表示重復(fù)前面的項0次或更多次
- +：表示重復(fù)前面的項1次或更多次
- ?：表示重復(fù)前面的項0次或1次
- {m}：表示重復(fù)前面的項m次
- {m，}：表示重復(fù)前面的項m次或更多次
- {m，n}：表示重復(fù)前面的項至少m次，不超過n次
- ()：匹配分組
- []：可選組
like與等價符號
- %代表0個或任意個字符
- _代表任意1個字符
- 如果想匹配%、_自身，可以使用反斜杠\轉(zhuǎn)義
- 可以使用escape指定轉(zhuǎn)義字符
CREATE TABLE public."user" (
id serial4 NOT NULL,
"name" varchar NULL,
CONSTRAINT newtable_pk PRIMARY KEY (id)
);
INSERT INTO public."user" ("name") VALUES
('bob'),
('boob'),
('bo%b'),
('Boob'),
('Bo%b'),
('BoB'),
('B_b'),
('BooB'),
('b_b');PostgreSQL的like比較靈活，可以有not like取反，也有ilike不區(qū)分大小寫
select * from public.user where name like 'al%'; select * from public.user where name like 'al_'; select * from public.user where name like 'bo%b'; select * from public.user where name like 'bo_b'; select * from public.user where name ilike 'bo_b'; select * from public.user where name like 'bo\%b'; select * from public.user where name like 'bo#%b' escape '#'; select * from public.user where name not like 'bo#%b' escape '#';
PostgreSQL還提供了如下與like等價的操作符：
- ~~：等價于like
- ~~*：like不區(qū)分大小寫
- !~~：等價于not like
- !~~*：not like不區(qū)分大小寫
select * from public.user where name ~~ 'bo_b'; select * from public.user where name ~~* 'bo_b'; select * from public.user where name !~~ 'bo\%b'; select * from public.user where name !~~* 'bo\%b';
正則匹配
PostgreSQL除了like，還支持正則匹配，這個就慎用了，可以作為的附加條件，而不要作為過濾的主要條件，特別是大表。
- ~：匹配正則表達式，區(qū)分大小寫
- ~*：匹配正則表達式，不區(qū)分大小寫
- !~：不匹配正則表達式，區(qū)分大小寫
- !~*：不匹配正則表達式，不區(qū)分大小寫
select * from public.user where name ~ '(B|b)oob'; select * from public.user where name ~* 'boob'; select * from public.user where name !~ 'boob'; select * from public.user where name !~* 'boob';
到此這篇關(guān)于PostgreSQL常用字符串函數(shù)與示例說明的文章就介紹到這了,更多相關(guān)PostgreSQL常用字符串函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在Docker環(huán)境中,對已運行的PostgreSQL數(shù)據(jù)庫實例進行密碼更改是一項常見的維護操作,下面將詳述如何通過一系列命令行操作來實現(xiàn)這一目標,需要的朋友可以參考下2024-07-07
這篇文章主要介紹了pgpool復(fù)制和負載均衡操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了mac裝PostgreSQL安裝失敗的問題及解決,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
這篇文章主要介紹了PostgreSQL 刪除check約束的實現(xiàn)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
這篇文章主要介紹了PostgreSQL忘記postgres賬號的密碼的解決方法,本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了postgresql 中的to_char()常用操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
PostgreSQL基礎(chǔ)知識之SQL操作符實踐指南
這篇文章主要給大家介紹了關(guān)于PostgreSQL基礎(chǔ)知識之SQL操作符實踐的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學(xué)習(xí)或者使用PostgreSQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2020-05-05
PostgreSQL 是一個功能強大的開源關(guān)系型數(shù)據(jù)庫系統(tǒng),基于 C 語言實現(xiàn),采用 PostgreSQL 許可證,這是一種自由軟件許可證,允許用戶自由使用、修改和分發(fā)源代碼,所以本文將給大家介紹PostgreSQL15.x安裝的詳細教程,需要的朋友可以參考下2024-09-09
什么是索引失效？如果where過濾條件設(shè)置不合理，即使索引存在，且where過濾條件中包含索引列，也會導(dǎo)致全表掃描，索引不起作用。什么條件下會導(dǎo)致索引失效呢2022-09-09
這篇文章主要介紹了PostgreSQL教程(十)：性能提升技巧,本文講解了使用EXPLAIN、批量數(shù)據(jù)插入、關(guān)閉自動提交、使用COPY、 刪除索引、刪除外鍵約束等技巧,需要的朋友可以參考下2015-05-05