SQL 字元串函數

今天一同學在群裡問我一個問題。 問：請教一個ＳＱＬ　２０００　ＳＥＶＥＲ問題： select * from itemcode where code like '40%' 如何讓code=40101001 變成code=401-01-001 目前有666個ＣＯＤＥ是類似40101001 用什麼語句能把它變成401-01-001 答： update itemcode set code=replace(code,left(code,8),left(code,3)+'-'+substring(code,4,2)+'-'+substring(code,6,3)) where code like '40%' 問： replace(code,left(code,8),left(code,3)+'-'+substring(code,4,2)+'-'+substring(code,6,3))是什麼意思? 答：拿 code=123456789 做例子 a = left(code,8) = 12345678 b = left(code,3) = 123 c = substring(code,4,2) = 45 d = substring(code,6,3) = 678 e = b+'-'+c+'-'+e = 123-45-678 f = replace(code,a,e) = 123-45-6789 配合下邊的來看，應該能明白了 1、left(code,8) 取前8位 2、substring(code,4,2) 從第4位開始取2位 3、+ 是字元串連接配接符 4、replace(a,str1,str2) 将a中的str1替換為str2 幾問幾答下來，我就想着要整理一份完整的SQL字元串函數出來，借以學習和友善以後查詢。 SQL字元串函數 字元串函數對二進制資料、字元串和表達式執行不同的運算。此類函數作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 資料類型以及可以隐式轉換為CHAR 或VARCHAR的資料類型。可以在SELECT 語句的SELECT 和WHERE 子句以及表達式中使用字元串函數。常用的字元串函數有： 一、字元轉換函數 1、ASCII() 傳回字元表達式最左端字元的ASCII 碼值。在ASCII（）函數中，純數字的字元串可不用‘’括起來，但含其它字元的字元串必須用‘’括起來使用，否則會出錯。 2、CHAR() 将ASCII 碼轉換為字元。如果沒有輸入0 ~ 255 之間的ASCII 碼值，CHAR（） 傳回NULL 。 3、LOWER()和UPPER() LOWER()将字元串全部轉為小寫；UPPER()将字元串全部轉為大寫。 4、STR() 把數值型資料轉換為字元型資料。 STR ([，length[， ]]) length 指定傳回的字元串的長度，decimal 指定傳回的小數位數。如果沒有指定長度，預設的length 值為10， decimal 預設值為0。當length 或者decimal 為負值時，傳回NULL；當length 小于小數點左邊（包括符号位）的位數時，傳回length 個*；先服從length ，再取decimal ；當傳回的字元串位數小于length ，左邊補足空格。 二、去空格函數 1、LTRIM() 把字元串頭部的空格去掉。 2、RTRIM() 把字元串尾部的空格去掉。 三、取子串函數 1、left() LEFT (， ) 傳回character_expression 左起 integer_expression 個字元。 2、RIGHT() RIGHT (， ) 傳回character_expression 右起 integer_expression 個字元。 3、SUBSTRING() SUBSTRING (， ， length) 傳回從字元串左邊第starting_ position 個字元起length個字元的部分。 四、字元串比較函數 1、CHARINDEX() 傳回字元串中某個指定的子串出現的開始位置。 CHARINDEX (<’substring_expression’>， ) 其中substring _expression 是所要查找的字元表達式，expression 可為字元串也可為列名表達式。如果沒有發現子串，則傳回0 值。此函數不能用于TEXT 和IMAGE 資料類型。 2、PATINDEX() 傳回字元串中某個指定的子串出現的開始位置。 PATINDEX (<’%substring _expression%’>， )其中子串表達式前後必須有百分号“%”否則傳回值為0。與CHARINDEX 函數不同的是，PATINDEX函數的子串中可以使用通配符，且此函數可用于CHAR、 VARCHAR 和TEXT 資料類型。 五、字元串操作函數 1、QUOTENAME() 傳回被特定字元括起來的字元串。 QUOTENAME (<’character_expression’>[， quote_ character]) 其中quote_ character 标明括字元串所用的字元，預設值為“[]”。 2、REPLICATE() 傳回一個重複character_expression 指定次數的字元串。 REPLICATE (character_expression integer_expression) 如果integer_expression 值為負值，則傳回NULL 。 3、REVERSE() 将指定的字元串的字元排列順序颠倒。 REVERSE () 其中character_expression 可以是字元串、常數或一個列的值。 4、REPLACE() 傳回被替換了指定子串的字元串。 REPLACE (， ， ) 用string_expression3 替換在string_expression1 中的子串string_expression2。 4、SPACE() 傳回一個有指定長度的空白字元串。 SPACE () 如果integer_expression 值為負值，則傳回NULL 。 5、STUFF() 用另一子串替換字元串指定位置、長度的子串。 STUFF (， ， ，) 如果起始位置為負或長度值為負，或者起始位置大于character_expression1 的長度，則傳回NULL 值。如果length 長度大于character_expression1 中 start_ position 以右的長度，則character_expression1 隻保留首字元。 六、資料類型轉換函數 1、CAST() CAST ( AS [ length ]) 2、CONVERT() CONVERT ([ length ]， [， style]) 1）data_type為SQL Server系統定義的資料類型，使用者自定義的資料類型不能在此使用。 2）length用于指定資料的長度，預設值為30。 3）把CHAR或VARCHAR類型轉換為諸如INT或SAMLLINT這樣的INTEGER類型、結果必須是帶正号或負号的數值。 4）TEXT類型到CHAR或VARCHAR類型轉換最多為8000個字元，即CHAR或VARCHAR資料類型是最大長度。 5）IMAGE類型存儲的資料轉換到BINARY或VARBINARY類型，最多為8000個字元。 6）把整數值轉換為MONEY或SMALLMONEY類型，按定義的國家的貨币機關來處理，如人民币、美元、英鎊等。 7）BIT類型的轉換把非零值轉換為1，并仍以BIT類型存儲。 8）試圖轉換到不同長度的資料類型，會截短轉換值并在轉換值後顯示“+”，以辨別發生了這種截斷。 9）用CONVERT（） 函數的style 選項能以不同的格式顯示日期和時間。style 是将DATATIME 和SMALLDATETIME 資料轉換為字元串時所選用的由SQL Server 系統提供的轉換樣式編号，不同的樣式編号有不同的輸出格式。 七、日期函數 1、day(date_expression) 傳回date_expression中的日期值 2、month(date_expression) 傳回date_expression中的月份值 3、year(date_expression) 傳回date_expression中的年份值 4、DATEADD() DATEADD (， ， ) 傳回指定日期date 加上指定的額外日期間隔number 産生的新日期。參數“datepart” 取值如下： 5、DATEDIFF() DATEDIFF (， ， ) 傳回兩個指定日期在datepart 方面的不同之處，即date2 超過date1的差距值，其結果值是一個帶有正負号的整數值。 6、DATENAME() DATENAME (， ) 以字元串的形式傳回日期的指定部分此部分。由datepart 來指定。 7、DATEPART() DATEPART (， ) 以整數值的形式傳回日期的指定部分。此部分由datepart 來指定。 DATEPART (dd， date) 等同于DAY (date) DATEPART (mm， date) 等同于MONTH (date) DATEPART (yy， date) 等同于YEAR (date) 8、GETDATE() 以DATETIME 的預設格式傳回系統目前的日期和時間 [最後修改由 keno, 于 2005-08-24 17:37:01]