SQL基础操作之字符串处理操作教程

7.6.1 生成自增值

需求：

通过SQL生成一个1到1000条记录.

解决方法:

通过CTE的递归来实现该需求.

SQLServer:

DECLARE @startINT, @endINT
SELECT @start=1, @end=1000
;WITH NumberSequence( Number) AS
(
    SELECT @start ASNumber
       UNION ALL
    SELECT Number + 1
       FROM NumberSequence
       WHERE Number <@end
)
SELECT * FROM NumberSequence OPTION(MaxRecursion 1000)

执行结构:

Number
1
2
3
4
5
6
7
8
9
10
…

Oracle:

WITH t(num)AS (
  SELECT 1 FROM DUAL
  UNION ALL
  SELECT t.num+1
  FROM t WHERE t.num<100
  )
SELECT * FROM t;

执行结果:

Num
1
2
3
4
5
6
7
8
9
10
…

Mysql(8.0及以上版本):

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

执行结果:

Num
1
2
3
4
5
6
7
8
9
10
…

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
KING	4
KING	5
KING	6
KING	7
KING	8
…	…

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 DUAL
  UNION ALL
  SELECT t.num+1
  FROM 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)

Mysql8.0:

WITH RECURSIVE cte (num)AS
(
  SELECT 1
  UNION ALL
  SELECT 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 TRAN
   SET IDENTITY_INSERTdeptON;
   GO
   INSERT 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 emp

SELECT 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）函数对含数字的部分进行替换.

SQLServer：

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

结果同上

Oracle：

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))

— 或者用如下方法,思路通过判断截取的每个字符的ASCII值来判断区间是否落在[48,57],[97,122],落在的为0,反之为1,然后再通过对该Flag进行sum来判断,如果等于0则说明数字字符型的.

SELECT data -- ,SUM(Flag)
FROM
(
SELECT LOWER(data) as data,SUBSTRING(e.data,seq.pos,1)AS ename_Split,ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) AS asci,
CASE WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1))>= 48 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=57 THEN 0
WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1))>= 97 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=122 THEN 0 ELSE 1 END AS Flag
FROM temp_str e,
(SELECT number AS pos FROM master.[dbo].[spt_values]WHERE type = 'P' AND number>0) seq
WHERE seq.pos<= LEN(e.data)
-- ORDER BY data
)A
GROUP BY data
HAVING SUM(Flag) = 0

Mysql:

SELECT data--,translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPEAT('a',36)) AS trans
FROM temp_str
WHERE translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPEAT('a',36))=REPEAT('a',LENGTH(data))

-- 利用正则表达式来匹配
SELECT data
FROM temp_str
WHERE data regexp'[^0-9A-Za-z]'=0
 
-- 待验证Mysql8

WITH RECURSIVE cte (num) AS
(
  SELECT 1
  UNION ALL
  SELECT num+1 FROM cte WHERE num <100
)
SELECT data-- ,SUM(Flag)
FROM
(
SELECT LOWER(data)as data,SUBSTRING(e.data,seq.pos,1) AS ename_Split,ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) AS asci,

CASE WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1))>=48 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=57 THEN 0

WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1))>=97 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=122 THEN 0 ELSE 1 END AS Flag
FROM temp_str e,
(SELECT num FROM cte) seq
WHERE seq.pos<=LEN(e.data)
-- ORDER BY data
)A
GROUP BY data
HAVING SUM(Flag)=0

Oracle:

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 data
    UNION ALL
SELECT 'Data,Arithmetic' AS data
    UNION ALL
SELECT 'Science中国' AS data
    UNION 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 data
    UNION ALL
SELECT 'Data,Arithmetic'AS data
    UNION ALL
SELECT 'Science中国' AS data
    UNION 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’ASdata

UNION ALL

SELECT ‘Data,Arithmetic’ASdata

UNION ALL

SELECT ‘Science中国’ASdata

UNION ALL

SELECT ‘上S海H’AS data

)A

WHERE data LIKE ‘%[吖-座]%’

或者利用PATINDEX函数进行判断

SELECTdata FROM

(

SELECT ‘Hello,World,SQL’ASdata

UNION ALL

SELECT ‘Data,Arithmetic’ASdata

UNION ALL

SELECT ‘Science中国’ASdata

UNION ALL

SELECT ‘上S海H’AS data

)A

WHERE PATINDEX(‘%[吖-座]%’,data)>0

执行结果:

data

Science中国

上S海H

Oracle:

7.6.9 合并多行到一行

需求：

将emp表里deptno相同的ename以逗号拼接在一起.

解决方法:

这里通过字符串合并函数完成该效果.如group_concat

SQL Server:

SELECT DISTINCT

deptno

, STUFF((

SELECT N’, ‘+ CAST([ename]ASVARCHAR(255))

FROM emp e1

WHERE e1.deptno= e2.deptno

FOR 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(enameASVARCHAR(100)),empno,1

FROM emp

UNION ALL

SELECT x.deptno,x.cnt,CAST(x.list+’,’+e.enameASVARCHAR(100)),e.empno,x.len+1

FROM emp e,x

WHERE e.deptno= x.deptno

AND e.empno> x.empno

)

SELECT deptno,listAS StrCombinefrom 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 xWHERE 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,listAS StrCombinefrom 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

GROUPBY deptno

ORDERBY 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_Split

FROM (SELECT ENAMEFROM emp) e,

(SELECT numberAS posFROMmaster.[dbo].[spt_values]WHEREtype = ‘P’ AND number>0) seq

WHERE seq.pos<=LEN(e.ename)

ORDER BY ename, ename_Split

)

SELECT DISTINCT

ename

, STUFF((

SELECT N”+ CAST(ename_SplitASVARCHAR(255))

FROM x e1

WHERE e1.ename= e2.ename

FOR 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)ORDERBYSUBSTRING(e.ENAME,seq.num,1)separator”)AS StrByAlph

FROM (SELECT ENAMEFROM emp) e,

(SELECT iAS numFROM tb_incr)seq

WHERE seq.num<=LENGTH(e.ename)

GROUPBY ename

ORDERBY ename,SUBSTRING(e.ENAME,seq.num,1)

注: 这里借助group_concat函数里的ORDER BY关键字,对已经排序的字母进行合并.

Oracle:

7.6.11 判断一个字符是否是数字

需求：

将临时表里判断data字段里哪些是数字.

解决方法:

这里通过函数isnumberic或者regexp完成该效果.

Mysql:

DELIMITER $$

DROP FUNCTIONIFEXISTS `IsNum` $$

CREATE FUNCTION `IsNum`(strVARCHAR(25)) RETURNSINT

BEGIN

DECLARE iResultINTDEFAULT0;

IFISNULL(str)THENreturn0;END IF;– NULL 字符串

IF str=”THENreturn0;END IF;– 空字符串

SELECT strREGEXP’^[0-9]*$’INTO iResult;

IF iResult=1THEN

RETURN1;

ELSE

RETURN0;

END IF;

END $$

DELIMITER ;

或者使用正则表达式:

SELECT dataFROM

(

SELECT’63’AS data

UNIONALL

SELECT’36(‘AS data

UNIONALL

SELECT’3(6’AS data

UNIONALL

SELECT'(36’AS data

UNIONALL

SELECT’36$’AS data

UNIONALL

SELECT”AS data

UNIONALL

SELECTNULLAS data

)A

— WHERE IsNum(data) = 1

WHERE data REGEXP’^[0-9]*$’=1AND data ISNOTNULLAND data<>”;

或者直接通过函数IsNum(data) = 1来判断,见注释部分.

SqlServer:

SELECT data FROM

(

SELECT ’63’ASdata

UNION ALL

SELECT ’36(‘ASdata

UNION ALL

SELECT ‘3(6’ASdata

UNION ALL

SELECT ‘(36’ASdata

UNION ALL

SELECT ’36$’ASdata

UNION ALL

SELECT ”AS data

UNION ALL

SELECT NULLASdata

)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 data

UNION ALL

SELECT ‘Data,Arithmetic’AS data

UNION ALL

SELECT ‘Science’ AS data

)A

执行结果:

StrSplit

Hello

Data

Science

 

或者借助parsename函数:

SELECT PARSENAME(REPLACE(data,’,’,’.’),2)AS StrSplit FROM

(

SELECT ‘Hello,World,SQL’ASdata

UNION ALL

SELECT ‘Data,Arithmetic’AS data

UNION ALL

SELECT ‘Science’ AS data

)A

WHERE PARSENAME(REPLACE(data,’,’,’.’),2)ISNOT 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 data

UNIONALL

SELECT’Data,Arithmetic’AS data

UNIONALL

SELECT’Science’AS data) e,

(SELECT ias numFROM tb_incr)seq

WHERE seq.num<=LENGTH(e.data)-LENGTH(REPLACE(e.data,’,’,”))+1

AND seq.num=2

ORDERBY 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函数执行示例见下:

SELECTSUBSTRING_INDEX(‘Hello,World,SQL’,’,’,1)ASSUBSTRING,1AS pos

UNIONALL

SELECTSUBSTRING_INDEX(‘Hello,World,SQL’,’,’,2)ASSUBSTRING,2AS pos

UNIONALL

SELECTSUBSTRING_INDEX(‘Hello,World,SQL’,’,’,3)ASSUBSTRING,3AS 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)

BEGIN

SET @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