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

需求描述

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

解決方法:這里需要自定義函數結合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 GOSELECT 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 )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 dataUNION ALL SELECT 'Data,Arithmetic' AS dataUNION 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

總結

以上是生活随笔為你收集整理的处理字符串_12_按照指定的位置截取字符的全部內容，希望文章能夠幫你解決所遇到的問題。

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