資料庫SQl語言最常用的字串函式

在Oracle,SQL Srever ,Mysql等主流資料庫中最常用的字串函式：

一.substr函式：

取得字串中指定起始位置和長度的字串預設是從起始位置到結束的子串。

substr( string，start_position,[length])    substr('目標字串',開始位置,長度)

如:
     substr('This is a test', 6, 2)     would return 'is'
     substr('This is a test', 6)     would return 'is a test'
     substr('TechOnTheNet', -3, 3)     would return 'Net'
     substr('TechOnTheNet', -6, 3)     would return 'The'select substr('Thisisatest', -4, 2) value from dual 

注意：開始位置（strat——postion）為負數時，將從右向左數。

二.replace函式

用第三個表示式替換第一個字串表示式中出現的所有第二個給定字串表示式。

1、語法

REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

 2、引數

'string_expression1'：待搜尋的字串表示式。string_expression1 可以是字元資料或二進位制資料。

'string_expression2'：待查詢的字串表示式。string_expression2 可以是字元資料或二進位制資料。

'string_expression3'：替換用的字串表示式。string_expression3 可以是字元資料或二進位制資料。

3、返回型別

如果 string_expression（1、2 或 3）是支援的字元資料型別之一，則返回字元資料。如果 string_expression（1、2 或 3）是支援的 binary 資料型別之一，則返回二進位制資料。

示例：

下例用 xxx 替換 abcdefghi 中的字串 cde。

SELECT REPLACE('abcdefghicde','cde','xxx') GO  
下面是結果集：

------------ abxxxfghixxx (1 row(s) affected)  

三.instr函式：

可以使用instr函式對某個字串進行判斷，判斷其是否含有指定的字元。

其語法為：
instr(sourceString,destString,start,appearPosition).   instr（'源字串' , '目標字串' ,'開始位置','第幾次出現'）

返回值為：查詢到的字串的位置

1.sourceString代表源字串；
2.destString代表想從源字串中查詢的子串；
3.start代表查詢的開始位置，該引數可選的，預設為1；
4.appearPosition代表想從源字元中查詢出第幾次出現的destString，該引數也是可選的，預設為1；

如果start的值為負數，那麼代表從右往左進行查詢，但是位置資料仍然從左向右計算。

對於instr函式，我們經常這樣使用：從一個字串中查詢指定子串的位置。例如：

SQL> select instr('yuechaotianyuechao','ao') position from dual;

POSITION
----------
         6

從第7個字元開始搜尋

SQL> select instr('yuechaotianyuechao','ao', 7) position from dual;


POSITION
----------
        17


從第1個字元開始，搜尋第2次出現子串的位置
SQL> select instr('yuechaotianyuechao','ao', 1, 2) position from dual;


POSITION
----------
        17

注意：

1.若找不到出現位置時返回結果為0，

2.這裡只有三個引數，意思是查詢第一個要查詢字元的位置(因為 ‘第幾次出現’預設為1),
   當‘起始位置’不大於要查詢的第一個字元的位置時，返回的值都將是第一個字元的位置，

   如果‘起始位置’大於要查詢的第一個字元的位置時，返回的值都將是第2個字元的位置,依此類推……

（但是也是以第一個字元開始計數）

四.oracle中length()與lengthb()函式：

SQL> select length('阿豬') from dual; 
LENGTH('阿豬') 
-------------- 
             2 

SQL> select lengthb('阿豬') from dual; 


LENGTHB('阿豬') 
--------------- 
              4 

區別：length求得是字元長度，lengthb求得是位元組長度。 

length返回的是字元數
lengthb返回的是位元組數

五.tranlste函式的用法：

translate(string,from_str,to_str)

    執行時,translate依次檢查string中的每個字元是否在from_str中存在，如果不存在，那麼這個string中的字元直接返回，如果存在，translate會記下這個字元在from_str中的位置，然後用to_str的同樣位置的字元代替string中的這個字元作業返回結果。

舉例如下：

   SQL> select translate('ac','ab2','00') from dual;
   結果：0c

   分析結果如下：a在ab2中進行查詢，位置是1，返回00中第一個字元0，然後c在ab2中進行查詢，不存在則直接返回c, 所以結果是0c,這是最簡單的，更個有空格或長度不一樣的！

   1.如果from_str可以比to_str長，也就是from_str的字元數目可以比to_str多，在from_str中多出來的字元稱為"額外字元"，也就是from_str中的位置在to_str中找不到的，比如
   SQL> select translate('ac1','abc123','abc') from dual;
   結果：ac

   2.這裡from_str中的1的位置是4，但是to_str的總長度是3，沒有4，所以在from_STR中的123都是額外字元
如果string中的字元在from_str的額外中出現，那麼string中這些字元將在返回時被刪除，所以1雖然在abc123
中出現，但是abc中不存在第四個字元，所以刪除返回值，結果為ac

 注意：to_str不能是NULL或者''，否則會返回空值，translate也不能用於CLOB，再說得簡單一點，也就是
 1、如果string中的字元如果在from_string中沒有，那麼返回時被保留
 2、如果string中的字元是from_string中的"額外字元"，那麼返回時被刪除
 3、如果string中的字元在from_string中找到，且在to_string中有相應位置的字元，那麼返回時用to_string中的字元替換string中的字元

六.replace函式

用第三個表示式替換第一個字串表示式中出現的所有第二個給定字串表示式。

1、語法

REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

 2、引數

'string_expression1'：待搜尋的字串表示式。string_expression1 可以是字元資料或二進位制資料。

'string_expression2'：待查詢的字串表示式。string_expression2 可以是字元資料或二進位制資料。

'string_expression3'：替換用的字串表示式。string_expression3 可以是字元資料或二進位制資料。

3、返回型別

如果 string_expression（1、2 或 3）是支援的字元資料型別之一，則返回字元資料。如果 string_expression（1、2 或 3）是支援的 binary 資料型別之一，則返回二進位制資料。

示例：

下例用 xxx 替換 abcdefghi 中的字串 cde。

SELECT REPLACE('abcdefghicde','cde','xxx') GO  
下面是結果集：

------------ abxxxfghixxx (1 row(s) affected)  

七.空格函式：

普通的空格:

前後的空格，使用LTrim和RTrim即可，例如：LTrim(RTrim(Name))
中間的空格，使用replace函式替換，例如：Replace(Name,' ','')

如果是普通的空格，很容易替換，但有時候會遇到一些特殊的空格，就比較麻煩，看起來跟普通的空格一模一樣，但就是無法替換掉。這就需要特殊的方法去找出空格，然後用replace替換。

找出這樣的空格，需要用到ascii函式，通過ascii函式，找出空格的ascii值，然後用replace函式+char函式來替換。

例如通過ascii函式，找到空格的ascii值為9，則使用replace(Name,char(9),'')來替換。

按照上面的方式，無非就是怎麼找出空格的ascii值。

假設Name有個值是“張三 ”，後面有個特殊的空格。

select ascii(replace(name,'張三','') from 表名 where id=**

把Name的值取出，然後替換掉文字部分，剩下的就是特殊的那個空格，就能通過ascii函式來獲取這個空格ascii值。