处理字符串_12_按照指定的位置截取字符

                           按照指定的位置截取字符

需求描述

需求：按照逗号拆分字符串,取拆分出来的第二个子串.

解决方法:这里需要自定义函数结合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)  
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

执行结果 

--或者借助parsename函数:
SELECT PARSENAME(REPLACE(data,',','.'),2) AS StrSplit FROM
(
SELECT 'Hello,World,SQL' AS data
  UNION ALL 
  SELECT 'Data,Arithmetic' AS data
  UNION ALL
  SELECT 'Science' AS data
)A
WHERE PARSENAME(REPLACE(data,',','.'),2) IS NOT NULL

--Mysql:
SELECT data,SUBSTRING_INDEX(SUBSTRING_INDEX(data,',',seq.num),',',-1) AS sub,seq.num AS subStrPos
FROM
(SELECT 'Hello,World,SQL' AS data
    UNION ALL 
SELECT 'Data,Arithmetic' AS data
    UNION ALL
SELECT 'Science' AS data) e,
(SELECT i as 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

步骤解析:

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