按照指定的位置截取字符
需求描述
需求：按照逗号拆分字符串,取拆分出来的第二个子串.
解决方法:这里需要自定义函数结合substring截取字符串,以达到该效果.
注: 数据库数据集SQL脚本详见如下链接地址
员工表结构和数据初始化SQL脚本
SQL代码
--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<>0 and @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) ENDGOSELECT dbo.strSplitIndex(data,',',1) AS StrSplit FROM (SELECT 'Hello,World,SQL' AS dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION ALLSELECT 'Science' AS data)A
执行结果
--或者借助parsename函数:SELECT PARSENAME(REPLACE(data,',','.'),2) AS StrSplit FROM(SELECT 'Hello,World,SQL' AS dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION ALLSELECT 'Science' AS data)AWHERE PARSENAME(REPLACE(data,',','.'),2) IS NOT NULL
--Mysql:SELECT data,SUBSTRING_INDEX(SUBSTRING_INDEX(data,',',seq.num),',',-1) AS sub,seq.num AS subStrPosFROM(SELECT 'Hello,World,SQL' AS dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION ALLSELECT 'Science' AS data) e,(SELECT i as num FROM tb_incr)seqWHERE seq.num<=LENGTH(e.data)-LENGTH(REPLACE(e.data,',',''))+1AND seq.num = 2ORDER BY data,seq.num
步骤解析:
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 posUNION ALLSELECT SUBSTRING_INDEX('Hello,World,SQL',',',2) AS SUBSTRING,2 AS posUNION ALLSELECT SUBSTRING_INDEX('Hello,World,SQL',',',3) AS SUBSTRING,3 AS pos
