函數和操作符-二 · postgresql手冊

### [PostgreSQL學習手冊(函數和操作符<二>)](http://www.cnblogs.com/stephen-liu74/archive/2012/05/04/2294643.html) Posted on?2012-05-04 09:42?[Stephen_Liu](http://www.cnblogs.com/stephen-liu74/)?閱讀(2165) 評論(2)?[編輯](http://www.cnblogs.com/stephen-liu74/admin/EditPosts.aspx?postid=2294643)?[收藏](http://www.cnblogs.com/stephen-liu74/archive/2012/05/04/2294643.html#)?![](https://box.kancloud.cn/2015-10-30_5632e1b6eb50a.jpg) **六、模式匹配：** ?? ?PostgreSQL中提供了三種實現模式匹配的方法：SQL LIKE操作符，更近一些的SIMILAR TO操作符，和POSIX-風格正則表達式。 ??? 1. LIKE： ?? ?string?**LIKE**?pattern [?**ESCAPE**?escape-character ] ?? ?string?**NOT LIKE**?pattern [?**ESCAPE**?escape-character ] ?? ?每個pattern定義一個字串的集合。如果該string包含在pattern代表的字串集合里，那么LIKE表達式返回真。和我們想象的一樣，如果LIKE返回真，那么NOT LIKE表達式返回假，反之亦然。在pattern里的下劃線(_)代表匹配任何單個字符，而一個百分號(%)匹配任何零或更多字符，如： *?? ?'abc' LIKE 'abc'???? true* *?? ?'abc' LIKE 'a%'???? true* *?? ?'abc' LIKE '_b_'??? true* *?? ?'abc' LIKE 'c'??????? false??* ?? ?要匹配文本的下劃線或者百分號，而不是匹配其它字符，在pattern里相應的字符必須前導轉義字符。缺省的轉義字符是反斜杠，但是你可以用ESCAPE子句指定一個。要匹配轉義字符本身，寫兩個轉義字符。我們也可以通過寫成**ESCAPE ''**的方式有效地關閉轉義機制，此時，我們就不能關閉下劃線和百分號的特殊含義了。 ?? ?關鍵字**ILIK**E可以用于替換LIKE，令該匹配就當前的區域設置是大小寫無關的。這個特性不是SQL標準，是PostgreSQL的擴展。操作符**~~**等效于**LIKE**，而**~~***對應**ILIKE**。還有**!~~**和**!~~***操作符分別代表**NOT LIKE**和**NOT ILIKE**。所有這些操作符都是PostgreSQL特有的。? ??? 2. SIMILAR TO正則表達式： ?? ?SIMILAR TO根據模式是否匹配給定的字符串而返回真或者假。 ?? ?string?**SIMILAR TO**?pattern [**ESCAPE**?escape-character] ?? ?string?**NOT SIMILAR TO**?pattern [**ESCAPE**?escape-character] ?? ?它和LIKE非常類似，支持LIKE的通配符(**'_'**和**'%'**)且保持其原意。除此之外，SIMILAR TO還支持一些自己獨有的元字符，如：?? ? ?? ?1).?**|**?標識選擇(兩個候選之一)。 ??? 2).?*****?表示重復前面的項零次或更多次。 ??? 3).?**+**?表示重復前面的項一次或更多次。 ??? 4). 可以使用圓括弧**()**把項組合成一個邏輯項。 ??? 5). 一個方括弧表達式**[...]**聲明一個字符表，就像POSIX正則表達式一樣。 ??? 見如下示例： *??? 'abc' SIMILAR TO 'abc'???? ? ? ? true* *?? ?'abc' SIMILAR TO 'a'?? ??? ?????? false* *?? ?'abc' SIMILAR TO '%(b|d)%'? true* *?? ?'abc' SIMILAR TO '(b|c)%'???? false* ?? ?帶三個參數的**substring**，substring(string from pattern for escape-character)，提供了一個從字串中抽取一個匹配SQL正則表達式模式的子字串的函數。和SIMILAR TO一樣，聲明的模式必須匹配整個數據串，否則函數失效并返回NULL。為了標識在成功的時候應該返回的模式部分，模式必須出現后跟雙引號(")的兩個轉義字符。匹配這兩個標記之間的模式的字串將被返回，如： *?? ?MyTest=# SELECT substring('foobar' from '%#"o_b#"%' FOR '#');?--這里#是轉義符，雙引號內的模式是返回部分。* ?? ? substring ?? ?----------- ?? ? oob ?? ?(1 row) *?? ?MyTest=# SELECT substring('foobar' from '#"o_b#"%' FOR '#');??--foobar不能完全匹配后面的模式，因此返回NULL。* ?? ? substring ?? ?----------- ?? ?(1 row) **七、數據類型格式化函數：** ?? ?PostgreSQL格式化函數提供一套有效的工具用于把各種數據類型(日期/時間、integer、floating point和numeric)轉換成格式化的字符串以及反過來從格式化的字符串轉換成指定的數據類型。下面列出了這些函數，它們都遵循一個公共的調用習慣：第一個參數是待格式化的值，而第二個是定義輸出或輸出格式的模板。 | **函數** | **返回類型** | **描述** | **例子** | |-----|-----|-----|-----| | to_char(timestamp, text) | text | 把時間戳轉換成字串 | to_char(current_timestamp, 'HH12:MI:SS') | | to_char(interval, text) | text | 把時間間隔轉為字串 | to_char(interval '15h 2m 12s', 'HH24:MI:SS') | | to_char(int, text) | text | 把整數轉換成字串 | to_char(125, '999') | | to_char(double precision, text) | text | 把實數/雙精度數轉換成字串 | to_char(125.8::real, '999D9') | | to_char(numeric, text) | text | 把numeric轉換成字串 | to_char(-125.8, '999D99S') | | to_date(text, text) | date | 把字串轉換成日期 | to_date('05 Dec 2000', 'DD Mon YYYY') | | to_timestamp(text, text) | timestamp | 把字串轉換成時間戳 | to_timestamp('05 Dec 2000', 'DD Mon YYYY') | | to_timestamp(double) | timestamp | 把UNIX紀元轉換成時間戳 | to_timestamp(200120400) | | to_number(text, text) | numeric | 把字串轉換成numeric | to_number('12,454.8-', '99G999D9S') | ?????1. 用于日期/時間格式化的模式： | **模式** | **描述** | |-----|-----| | HH | 一天的小時數(01-12) | | HH12 | 一天的小時數(01-12) | | HH24 | 一天的小時數(00-23) | | MI | 分鐘(00-59) | | SS | 秒(00-59) | | MS | 毫秒(000-999) | | US | 微秒(000000-999999) | | AM | 正午標識(大寫) | | Y,YYY | 帶逗號的年(4和更多位) | | YYYY | 年(4和更多位) | | YYY | 年的后三位 | | YY | 年的后兩位 | | Y | 年的最后一位 | | MONTH | 全長大寫月份名(空白填充為9字符) | | Month | 全長混合大小寫月份名(空白填充為9字符) | | month | 全長小寫月份名(空白填充為9字符) | | MON | 大寫縮寫月份名(3字符) | | Mon | 縮寫混合大小寫月份名(3字符) | | mon | 小寫縮寫月份名(3字符) | | MM | 月份號(01-12) | | DAY | 全長大寫日期名(空白填充為9字符) | | Day | 全長混合大小寫日期名(空白填充為9字符) | | day | 全長小寫日期名(空白填充為9字符) | | DY | 縮寫大寫日期名(3字符) | | Dy | 縮寫混合大小寫日期名(3字符) | | dy | 縮寫小寫日期名(3字符) | | DDD | 一年里的日子(001-366) | | DD | 一個月里的日子(01-31) | | D | 一周里的日子(1-7；周日是1) | | W | 一個月里的周數(1-5)(第一周從該月第一天開始) | | WW | 一年里的周數(1-53)(第一周從該年的第一天開始) | ?????2. 用于數值格式化的模板模式： | **模式** | **描述** | |-----|-----| | 9 | 帶有指定數值位數的值 | | 0 | 帶前導零的值 | | .(句點) | 小數點 | | ,(逗號) | 分組(千)分隔符 | | PR | 尖括號內負值 | | S | 帶符號的數值 | | L | 貨幣符號 | | D | 小數點 | | G | 分組分隔符 | | MI | 在指明的位置的負號(如果數字 < 0) | | PL | 在指明的位置的正號(如果數字 > 0) | | SG | 在指明的位置的正/負號 | **八、時間/日期函數和操作符：** ?? ?1. 下面是PostgreSQL中支持的時間/日期操作符的列表： | **操作符** | **例子** | **結果** | |-----|-----|-----| | + | date '2001-09-28' + integer '7' | date '2001-10-05' | | + | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00' | | + | date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00' | | + | interval '1 day' + interval '1 hour' | interval '1 day 01:00' | | + | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00' | | + | time '01:00' + interval '3 hours' | time '04:00' | | - | - interval '23 hours' | interval '-23:00' | | - | date '2001-10-01' - date '2001-09-28' | integer '3' | | - | date '2001-10-01' - integer '7' | date '2001-09-24' | | - | date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00' | | - | time '05:00' - time '03:00' | interval '02:00' | | - | time '05:00' - interval '2 hours' | time '03:00' | | - | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00' | | - | interval '1 day' - interval '1 hour' | interval '23:00' | | - | timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00' | | * | interval '1 hour' * double precision '3.5' | interval '03:30' | | / | interval '1 hour' / double precision '1.5' | interval '00:40' | ????2. 日期/時間函數： | **函數** | **返回類型** | **描述** | **例子** | **結果** | |-----|-----|-----|-----|-----| | age(timestamp, timestamp) | interval | 減去參數，生成一個使用年、月的"符號化"的結果 | age('2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days | | age(timestamp) | interval | 從current_date減去得到的數值 | age(timestamp '1957-06-13') | 43 years 8 mons 3 days | | current_date | date | 今天的日期 | ? | ? | | current_time | time | 現在的時間 | ? | ? | | current_timestamp | timestamp | 日期和時間 | ? | ? | | date_part(text, timestamp) | double | 獲取子域(等效于extract) | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 | | date_part(text, interval) | double | 獲取子域(等效于extract) | date_part('month', interval '2 years 3 months') | 3 | | date_trunc(text, timestamp) | timestamp | 截斷成指定的精度 | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00+00 | | extract(field from timestamp) | double | 獲取子域 | extract(hour from timestamp '2001-02-16 20:38:40') | 20 | | extract(field from interval) | double | 獲取子域 | extract(month from interval '2 years 3 months') | 3 | | localtime | time | 今日的時間 | ? | ? | | localtimestamp | timestamp | 日期和時間 | ? | ? | | now() | timestamp | 當前的日期和時間(等效于 current_timestamp) | ? | ? | | timeofday() | text | 當前日期和時間 | ? | ? | ????3. EXTRACT，date_part函數支持的field： | **域** | **描述** | **例子** | **結果** | |-----|-----|-----|-----| | CENTURY | 世紀 | EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); | 20 | | DAY | (月分)里的日期域(1-31) | EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); | 16 | | DECADE | 年份域除以10 | EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40'); | 200 | | DOW | 每周的星期號(0-6；星期天是0) (僅用于timestamp) | EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); | 5 | | DOY | 一年的第幾天(1 -365/366) (僅用于 timestamp) | EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40'); | 47 | | HOUR | 小時域(0-23) | EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40'); | 20 | | MICROSECONDS | 秒域，包括小數部分，乘以 1,000,000。 | EXTRACT(MICROSECONDS from TIME '17:12:28.5'); | 28500000 | | MILLENNIUM | 千年 | EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40'); | 3 | | MILLISECONDS | 秒域，包括小數部分，乘以 1000。 | EXTRACT(MILLISECONDS from TIME '17:12:28.5'); | 28500 | | MINUTE | 分鐘域(0-59) | EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40'); | 38 | | MONTH | 對于timestamp數值，它是一年里的月份數(1-12)；對于interval數值，它是月的數目，然后對12取模(0-11) | EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); | 2 | | QUARTER | 該天所在的該年的季度(1-4)(僅用于 timestamp) | EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); | 1 | | SECOND | 秒域，包括小數部分(0-59[1]) | EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40'); | 40 | | WEEK | 該天在所在的年份里是第幾周。 | EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); | 7 | | YEAR | 年份域 | EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); | 2001 | ????4. 當前日期/時間： ?? ?我們可以使用下面的函數獲取當前的日期和/或時間∶ ?? ?CURRENT_DATE ?? ?CURRENT_TIME ?? ?CURRENT_TIMESTAMP ?? ?CURRENT_TIME (precision) ?? ?CURRENT_TIMESTAMP (precision) ?? ?LOCALTIME ?? ?LOCALTIMESTAMP ?? ?LOCALTIME (precision) ?? ?LOCALTIMESTAMP (precision) *??? 該博客中提供的所有信息均源自PostgreSQL官方文檔，編寫該篇博客的主要目的是便于今后的查閱，特此聲明。* 分類:?[PostgreSQL](http://www.cnblogs.com/stephen-liu74/category/343171.html)