目錄

?

7.6 處理字符串

7.6.1 生成自增值

7.6.2 遍歷字符串里的每個值

7.6.3 處理含引號的字符串

7.6.4 計算某個字符出現的次數

7.6.5 字符串里過濾不需要的字符

7.6.6 拆分字符串里的字符和數字

7.6.7 判斷字符串是字符串數字型

7.6.8 判斷字符串含有漢字

7.6.9 合并多行到一行

7.6.10 對字符串重新按字母排序重新組合

7.6.11 判斷一個字符是否是數字

7.6.12 按照指定的位置截取字符

7.6.13 按照指定的分隔符截取字符返回表形式

---

7.6 處理字符串

注:數據集和表結構見?SQL基礎操作_1_檢索數據

7.6.1 生成自增值

需求：通過SQL生成一個1到1000條記錄.

解決方法:通過CTE的遞歸來實現該需求.

SQLServer:

DECLARE @startINT, @endINT SELECT @start=1, @end=1000 ;WITH NumberSequence( Number) AS (SELECT @start ASNumberUNION ALLSELECT Number + 1FROM NumberSequenceWHERE Number <@end ) SELECT * FROM NumberSequence OPTION (MaxRecursion 1000)

執行結果:

Number

1

2

3

...

?

Oracle:

WITH t(num) AS (SELECT 1 FROM DUALUNION ALLSELECT t.num+1FROM t WHERE t.num<100) SELECT * FROM t;

執行結果:

Num

1

2

3

...

?

Mysql(8.0及以上版本):

WITH RECURSIVE cte (num) AS (SELECT 1UNION ALLSELECT num+1 FROM cte WHERE num <100 ) SELECT * FROM cte;

執行結果:

Num

1

2

3

?

…

?

7.6.2 遍歷字符串里的每個值

需求：打印出ename為’King’的名字里每一個字母,每個字母占一行.

解決方法:通過自增表和emp表先cross join(笛卡爾積),然后再通過ename的len(ename的長度)進行過濾,最終得到顯示每個字母的結果.

SQLServer:

SELECT SUBSTRING (e.ENAME,seq.pos,1) AS ename_Split FROM (SELECT ENAME FROM emp WHERE ename= 'KING' ) e, (SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0)seq WHERE seq.pos <= LEN(e.ename)

執行結果:

ename_Split

K

I

N

G

注:

1: 這里master.[dbo].[spt_values]是一張特殊的系統視圖,里面存了從0到2047總2048條自增序列.

2: 如果不明白,可以分段來看.

Step1:

SELECT e.*,seq.* FROM (SELECT ENAME FROM emp WHERE ename= 'KING' ) e, (SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0)seq

執行結果:

ENAME	pos
KING	1
KING	2
KING	3
?	?
…	…

Sept2:

SELECT SUBSTRING(e.ENAME,seq.pos,1)AS ename_Split FROM .. e, ..seq WHERE seq.pos<=LEN(e.ename)

這里通過SUBSTRNG函數,每次的開始位置不斷調整,每次僅取一個字符,再通過LEN函數過濾.所以得到最終結果.如果不熟悉SUBSTRING的語法,這里簡單介紹下.

SUBSTRING ( expression, start, length )

1) 參數expression是要截取的原始字符串,比如這里的” KING”

2) 參數start是要截取的位置,比如從第2個位置開始,那應該從” I”往后數.

3) 參數length是要截取的長度,沿用上一行的例子,如果長度定義為2,則最終截取字符串是”IN”

Oracle:

WITH t(num) AS (SELECT 1 FROM DUALUNION ALLSELECT t.num+1FROM t WHEREt.num<100) SELECT SUBSTR(e.ENAME,seq.num,1) AS ename_Split FROM (SELECT ENAME FROM emp WHERE ename ='KING' ) e, (SELECT num FROM t)seq WHERE seq.num <= LENGTH(e.ename)

?Mysql 8.0:

WITH RECURSIVE cte (num)AS (SELECT 1UNION ALLSELECT num+1 FROM cte WHERE num <100 ) SELECT SUBSTRING(e.ENAME,seq.num,1) AS ename_Split FROM (SELECT ENAME FROM emp WHERE ename ='KING' ) e, (SELECT num FROM cte)seq WHERE seq.num <= LENGTH(e.ename)

?

7.6.3 處理含引號的字符串

需求：往dept表里插入dname為Test’s,loc為Beijing,deptno為100的數據.

解決方法:這里有位引號是特殊符號,所以需要特殊處理,比如如果雙引號包裹起來.

Mysql:

INSERT INTO dept VALUES(100,'Test\'s','Beijing');

Sql Server:

BEGIN TRANSET IDENTITY_INSERTdeptON;GOINSERT INTO dept(deptno,dname,loc) VALUES (100,'Test''s','Beijing');SELECT * FROM dept WHERE deptno=100;SET IDENTITY_INSERTdeptOFF;GO ROLLBACK TRAN

執行結果:

deptno	dname	loc
100	Test's	Beijing

?

7.6.4 計算某個字符出現的次數

需求：查詢emp表emptno是7499的用戶的job里S出現的次數.

解決方法:這里length(len)結合replace函數算出字符串出現的次數.

Sql Server:

SELECT empno,job,(LEN(JOB) - LEN(REPLACE(JOB,'S','')))/LEN('S') AS StrFreq FROM emp WHERE empno=7499;

empno	job	StrFreq
7499	SALESMAN	2

Mysql:

SELECT empno,job,ROUND((LENGTH(JOB) - LENGTH(REPLACE(JOB,'S','')))/LENGTH('S')) AS StrFreq FROM emp WHERE empno=7499;

注:這里除以LENGTH('S')是為了考慮傳入的字符串是2位以及以上的情況,比如’SS’.

?

7.6.5 字符串里過濾不需要的字符

需求：過濾tmp_v視圖里含數字的部分. 其中tmp_v視圖的data字段的定義是emp表的ename字段拼接空格和deptno字段。

解決方法:這里通過translate函數對含數字的部分進行替換.

SQL Server:

create view tmp_v AS SELECT ename+' '+cast(deptno as varchar)as data from empSELECT data,replace(dbo.translate(data,'0123456789','@@@@@@@@@@'),'@','') as ename FROM tmp_v order by replace(dbo.translate(data,'0123456789','@@@@@@@@@@'),'@','') desc

data	ename
WARD 30	WARD
TURNER 30	TURNER
SMITH 20	SMITH
SCOTT 20	SCOTT
MILLER 10	MILLER
MARTIN 30	MARTIN
KING 10	KING
JONES 20	JONES
JAMES 30	JAMES
FORD 20	FORD
CLARK 10	CLARK
BLAKE 30	BLAKE
ALLEN 30	ALLEN
ADAMS 20	ADAMS

注：這里需要參考之前章節里translate函數的實現.

Mysql：

create view tmp_v AS SELECT CONCAT(ename,' ',deptno) as data from emp SELECT data,replace(translate(data,'0123456789','@@@@@@@@@@'),'@','') as ename FROM tmp_v order by replace(translate(data,'0123456789','@@@@@@@@@@'),'@','') desc

Oracle：

?

7.6.6 拆分字符串里的字符和數字

需求：過濾tmp_v視圖里data字段拆分會原來的ename和deptno兩個字段.

解決方法:這里通過translate、replace、repeate（replicate、rpad）函數對含數字的部分進行替換.

SQL Server：

SELECT data,replace(dbo.translate(data,'0123456789',REPLICATE('@',10)),'@','') as ename, replace(dbo.translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPLICATE('@',26)),'@','') as deptno FROM tmp_v order by replace(dbo.translate(data,'0123456789',REPLICATE('@',10)),'@','') desc

data	ename	deptno
WARD 30	WARD	?30
TURNER 30	TURNER	?30
SMITH 20	SMITH	?20
SCOTT 20	SCOTT	?20
MILLER 10	MILLER	?10
MARTIN 30	MARTIN	?30
KING 10	KING	?10
JONES 20	JONES	?20
JAMES 30	JAMES	?30
FORD 20	FORD	?20
CLARK 10	CLARK	?10
BLAKE 30	BLAKE	?30
ALLEN 30	ALLEN	?30
ADAMS 20	ADAMS	?20

Mysql:

SELECT data,replace(translate(data,'0123456789',REPEAT('@',10)),'@','') as ename, replace(translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPEAT('@',26)),'@','') as deptno FROM tmp_v order by replace(translate(data,'0123456789',REPEAT('@',10)),'@','') desc

?

?

7.6.7 判斷字符串是字符串數字型

需求：檢索temp_strdata表的字段data是字符串數字類型的記錄這里如果都是字符串或者數字的也符合條件.

解決方法:這里通過translate、replace、repeate（replicate、rpad）函數對含數字的部分進行替換.

SqlServer：

CREATE TABLE temp_str(data VARCHAR(1000)); INSERT INTO temp_str VALUES('SMITH20'); INSERT INTO temp_str VALUES('JONES30'); INSERT INTO temp_str VALUES('Jim#40'); INSERT INTO temp_str VALUES('50$Tom'); INSERT INTO temp_str VALUES('60:Mike'); INSERT INTO temp_str VALUES('70Cruz'); INSERT INTO temp_str VALUES('Jack'); INSERT INTO temp_str VALUES('J8oh0n');SELECT data --,dbo.translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPLICATE('a',36)) as trans FROM temp_str WHERE dbo.translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPLICATE('a',36))=REPLICATE('a',LEN(data))

7.6.8 判斷字符串含有漢字

需求：檢索含有漢字的字符串.

解決方法:這里通過函數CHAR_LENGTH對比LENGTH進行對比來判斷.

Mysql:

SELECT data,LENGTH(data) AS Len_data,CHAR_LENGTH(data) AS CharLen_Data,HEX(data) AS HexData FROM ( SELECT 'Hello,World,SQL'AS dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION ALL SELECT 'Science中國' AS dataUNION ALL SELECT '上S海H' AS data )A WHERE LENGTH(data) <> CHAR_LENGTH(data)

執行結果:

data	Len_data	CharLen_Data
Science中國	13	9
上S海H	8	4

注:

LENGTH()?returnsthe length of the?string measured in bytes.?
CHAR_LENGTH()?returns the length of the?string measured incharacters.

LENGTH:是計算字節的長度.一個漢字是算三個字符,一個數字或字母算一個字符

CHAR_LENGTH:漢字、數字、字母都算是一個字符

或者通過字符串的十六進制并結合REGEXP來判斷.

SELECT data,HEX(data) AS HexData FROM ( SELECT 'Hello,World,SQL' AS dataUNION ALL SELECT 'Data,Arithmetic'AS dataUNION ALL SELECT 'Science中國' AS dataUNION ALL SELECT '上S海H' AS data )A WHERE HEX(data) REGEXP'^(..)*(E[4-9])'

執行結果:

data	HexData
Science中國	536369656E6365E4B8ADE59BBD
上S海H	E4B88A53E6B5B748

SQL Server:

SELECT data FROM ( SELECT 'Hello,World,SQL' AS dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION ALL SELECT 'Science中國' AS dataUNION ALL SELECT '上S海H' AS data )A WHERE data LIKE '%[吖-座]%'

?

-- 或者利用PATINDEX函數進行判斷 SELECT data FROM ( SELECT 'Hello,World,SQL' AS dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION ALL SELECT 'Science中國' AS dataUNION ALL SELECT '上S海H' AS data )A WHERE PATINDEX ('%[吖-座]%',data) > 0

執行結果:

data

Science中國

上S海H

?

7.6.9 合并多行到一行

需求：將emp表里deptno相同的ename以逗號拼接在一起.

解決方法:這里通過字符串合并函數完成該效果.如group_concat

SQL Server:

SELECT DISTINCTdeptno, STUFF((SELECT N', '+ CAST([ename]ASVARCHAR(255))FROM emp e1WHERE e1.deptno= e2.deptnoFOR XML PATH ('')), 1, 2,'')AS StrCombine FROM emp e2

執行結果:

Deptno	StrCombine
10	CLARK, KING, MILLER
20	SMITH, JONES, SCOTT, ADAMS, FORD
30	ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES

或者借助CTE:

WITH x(deptno,cnt,list,empno,len) AS( SELECT deptno,COUNT(*) OVER(PARTITIONBY deptno),CAST(ename AS VARCHAR(100)),empno,1 FROM emp UNION ALL SELECT x.deptno,x.cnt,CAST(x.list+','+e.ename AS VARCHAR(100)),e.empno,x.len+1 FROM emp e,x WHERE e.deptno= x.deptno AND e.empno> x.empno ) SELECT deptno,list AS StrCombine from X WHERE len=cnt ORDER BY 1

步驟分析:

Step1: 首先借助COUNT(*) OVER(PARTITION BY deptno)確定每個部門里有多少員工

Step2: 借助len,初始值為1,完成自增(遞歸里 x.len+1)

Step3: 借助x.list+','+e.ename完成ename的拼接

Step4: 借助e.deptno = x.deptno AND e.empno >x.empno確定要遞歸的結果集

Step5: 查詢滿足條件的記錄,即按deptno分組的empno數和自增序號相同的記錄.

分步查看結果:

1)? 查看構建的遞歸數據.這里以depto=10的為例:

SELECT * FROM x WHERE deptno= 10

deptno	cnt	list	empno	len
10	3	CLARK	7782	1
10	3	KING	7839	1
10	3	MILLER	7934	1
10	3	KING,MILLER	7934	2
10	3	CLARK,KING	7839	2
10	3	CLARK,MILLER	7934	2
10	3	CLARK,KING,MILLER	7934	3

2)? 不難發現,這里高亮處的數據是我們想要的,所以通過如下方式獲取最終結果:

SELECT deptno,list AS StrCombine FROM X WHERE len=cnt ORDER BY 1

延展閱讀:如果僅想獲得某一個分組下的字符串合并,也可以按照如下方法:

DECLARE @combinedString VARCHAR(MAX) SELECT @combinedString = COALESCE(@combinedString+', ','')+ ename FROM emp WHERE deptno=10 SELECT @combinedString as StringValue

Mysql:

SELECT deptno,group_concat(ename) AS StrCombine FROM emp GROUP BY deptno ORDER BY emp.deptno,ename

?

7.6.10 對字符串重新按字母排序重新組合

需求：將emp表里ename按照字母順序重新組合生成新的字符.

解決方法:這里通過字符串合并函數或者結合substring和row_number完成該效果.

SqlServer:

WITH x(ename,ename_Split) AS ( SELECT TOP 100000 ename,SUBSTRING(e.ENAME,seq.pos,1) AS ename_SplitFROM (SELECT ENAME FROM emp) e,(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type= 'P' AND number>0) seqWHERE seq.pos <= LEN(e.ename)ORDER BY ename, ename_Split)SELECT DISTINCTename, STUFF((SELECT N''+ CAST(ename_SplitASVARCHAR(255))FROM x e1WHERE e1.ename= e2.enameFOR XML PATH ('')),1,0,'')AS StrByAlph FROM x e2

注: 如果想在CTE里使用[]排序,需要在查詢里指定TOP.

消息 1033，級別 15，狀態 1，第 67 行

除非另外還指定了 TOP、OFFSET 或 FORXML，否則，ORDER BY 子句在視圖、內聯函數、派生表、子查詢和公用表表達式中無效。

執行結果:

ename	StrByAlph
ADAMS	AADMS
ALLEN	AELLN
BLAKE	ABEKL
CLARK	ACKLR
FORD	DFOR
JAMES	AEJMS
JONES	EJNOS
KING	GIKN
MARTIN	AIMNRT
MILLER	EILLMR
SCOTT	COSTT
SMITH	HIMST
TURNER	ENRRTU
WARD	ADRW
?	?

?

Mysql:

SELECT ename,group_concat(SUBSTRING(e.ENAME,seq.num,1) ORDER BY SUBSTRING(e.ENAME,seq.num,1) separator '') AS StrByAlph FROM (SELECT ENAME FROM emp) e,(SELECT i AS num FROM tb_incr)seq WHERE seq.num <= LENGTH(e.ename) GROUP BY ename ORDER BY ename,SUBSTRING(e.ENAME,seq.num,1)

?

注: 這里借助group_concat函數里的ORDER BY關鍵字,對已經排序的字母進行合并.

這里tb_incr是個自增表,存放從1開始步長為1的自增序列

Oracle:

?

7.6.11 判斷一個字符是否是數字

需求：將臨時表里判斷data字段里哪些是數字.

解決方法:這里通過函數isnumberic或者regexp完成該效果.

Mysql:

DELIMITER $$ DROP FUNCTION IF EXISTS `IsNum` $$ CREATE FUNCTION `IsNum`(str VARCHAR(25)) RETURNS INT BEGINDECLARE iResult INT DEFAULT 0;IF ISNULL(str) THEN return 0;END IF;-- NULL 字符串IF str='' THEN return 0;END IF;-- 空字符串SELECT str REGEXP '^[0-9]*$' INTO iResult;IF iResult =1 THENRETURN 1;ELSERETURN 0;END IF;END $$ DELIMITER ;

或者使用正則表達式:

SELECT data FROM ( SELECT '63' AS data UNION ALL SELECT '36(' AS data UNION ALL SELECT '3(6' AS data UNION ALL SELECT '(36' AS data UNION ALL SELECT '36$' AS data UNION ALL SELECT '' AS data UNION ALL SELECT NULL AS data )A -- WHERE IsNum(data) = 1 WHERE data REGEXP '^[0-9]*$'=1 AND data IS NOT NULL AND data<> '';

或者直接通過函數IsNum(data) = 1來判斷,見注釋部分.

SqlServer:

SELECT data FROM ( SELECT '63' AS data UNION ALL SELECT '36(' AS data UNION ALL SELECT '3(6' AS data UNION ALL SELECT '(36' AS data UNION ALL SELECT '36$'AS data UNION ALL SELECT '' AS data UNION ALL SELECT NULL AS data )A WHERE ISNUMERIC(data)= 1

執行結果:

Data

63

?

7.6.12 按照指定的位置截取字符

需求：按照逗號拆分字符串,取拆分出來的第二個子串.

解決方法:這里需要自定義函數結合substring截取字符串,以達到該效果.

SQL Server:

CREATE FUNCTION strSplitIndex ( @str VARCHAR(1024), --要分割的字符串@split VARCHAR(10), --分隔符@index INT --要取元素的位置 ) RETURNS VARCHAR(1024) AS BEGIN DECLARE @location INT DECLARE @start INT DECLARE @next INT DECLARE @seed INT SET @str=LTRIM(RTRIM(@str)) SET @start=1 SET @next=1 SET @seed=LEN(@split) SET @location=CHARINDEX(@split,@str) WHILE @location<>0and @index>@next BEGIN SET @start=@location+@seed SET @location=CHARINDEX(@split,@str,@start) SET @next=@next+1 END IF @location =0 SELECT @location =LEN(@str)+1 RETURN SUBSTRING(@str,@start,@location-@start) END GO SELECT dbo.strSplitIndex(data,',',1) AS StrSplit FROM (SELECT 'Hello,World,SQL' AS dataUNION ALLSELECT 'Data,Arithmetic' AS dataUNION ALLSELECT 'Science' AS data)A

執行結果:

StrSplit

Hello

Data

Science

或者借助parsename函數:

SELECT PARSENAME(REPLACE(data,',','.'),2) AS StrSplit FROM (SELECT 'Hello,World,SQL' AS dataUNION ALLSELECT 'Data,Arithmetic' AS dataUNION ALLSELECT 'Science' AS data )A WHERE PARSENAME(REPLACE(data,',','.'),2)IS NOT NULL

執行結果:

StrSplit

Hello

Data

Mysql:

SELECT data,SUBSTRING_INDEX(SUBSTRING_INDEX(data,',',seq.num),',',-1) AS sub,seq.num AS subStrPos FROM (SELECT 'Hello,World,SQL' AS dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION ALL SELECT 'Science' AS data) e, (SELECT ias num FROM tb_incr)seq WHERE seq.num <= LENGTH(e.data)- LENGTH(REPLACE(e.data,',',''))+1 AND seq.num=2 ORDER BY data,seq.num

執行結果:

data	sub	subStrPos
Data,Arithmetic	Arithmetic	2
Hello,World,SQL	World	2

步驟解析:

Step1: 首先借助自增表將data字段里的數據按照逗號的數目切分,如果有2個逗號,則會切分成3部分

Step2: 借助SUBSTRING_INDEX函數截取逗號所在位置的子串,這里鑒于SUBSTRING_INDEX的第三個參數的意義是子串累加,所以又套了個SUBSTRING_INDEX,第三個參數傳-1,即從右邊截取.

Step3:借助自增表的num,取指定分割位置的數據,這里是2.

注: SUBSTRING_INDEX函數執行示例見下:

SELECT SUBSTRING_INDEX('Hello,World,SQL',',',1) AS SUBSTRING,1 AS pos UNION ALL SELECT SUBSTRING_INDEX('Hello,World,SQL',',',2) AS SUBSTRING,2 AS pos UNION ALL SELECT SUBSTRING_INDEX('Hello,World,SQL',',',3) AS SUBSTRING,3 AS pos

執行結果:

SUBSTRING	pos
Hello	1
Hello,World	2
Hello,World,SQL	3

?

Oracle:

?

7.6.13 按照指定的分隔符截取字符返回表形式

需求：按照逗號拆分字符串,并指定返回的格式是表.

解決方法:這里需要自定義函數結合substring截取字符串,以達到該效果.

SQLServer:

CREATE FUNCTION strSplitTable(@strNVARCHAR(2000),@splitNVARCHAR(2)) RETURNS @t TABLE(SubStr VARCHAR(1000)) AS BEGIN DECLARE @tmpSubStr VARCHAR(1000),@getIndexINT SET @getIndex=CHARINDEX(',',@str) WHILE(@getIndex<>0) BEGINSET @tmpSubStr=CONVERT(VARCHAR(1000),SUBSTRING(@str,1,@getIndex-1))INSERT INTO @t(SubStr)VALUES(@tmpSubStr)SET @str=STUFF(@str,1,@getIndex,'')SET @getIndex=CHARINDEX(',',@str) END INSERT INTO @t(SubStr)VALUES(@str) RETURN END GO SELECT * FROM strSplitTable('Hello,World,SQL',',')

執行結果:

SubStr

Hello

World

SQL

注:? 自Sql Server 2016已新增系統函數STRING_SPLIT,測試示例見下:

SELECT A.value value_A,B.value value_B FROM ( SELECT value FROM STRING_SPLIT('A$B$C','$') )A LEFT JOIN (SELECT value FROM STRING_SPLIT('A,B',',') )B ON A.value = B.value

執行結果:

value_A	value_B
A	A
B	B
C	NULL

這里僅支持單個字符的分隔符.

消息 214，級別 16，狀態 11，第 12 行
Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'.

Mysql:

未見該方法.

總結

以上是生活随笔為你收集整理的SQL基础操作_5_字符串处理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。