SQL - 如何基于分隔符拆分字符串？ [英] SQL - How do I split a string based on a delimiter?

问题描述

itemcode



SER_ELECTR_003

SER_MACHRENTL_004

CI_BRI_0005

CI_CEM_00001

这是我的专栏



1）必需的输出

itemcode

SER_ELECTR_003
SER_MACHRENTL_004
CI_BRI_0005
CI_CEM_00001
This is my column

1)REQUIRED OUTPUT

ITEMCODE             PART1  PART2     PART3
SER_ELECTR_003       SER    ELECT      003
SER_MACHRENTL_004    SER   MACHRENTL   004
CI_BRI_00005          CI     BRI      0005
CI_CEM_00001          CI     CEM      00001

推荐答案

首先创建一个通用split [ ^ ]功能，如所以： -

First creaste a generic "split"[^] function, like so:-

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

然后在您的查询中使用它，例如喜欢： -

Then use it in your query e.g. like:-

select *
from fnSplit('SER_ELECTR_003', '_') 

关于OP的评论：

As to OP's comment:

会员11917248写道：

Member 11917248 wrote:

我不可能以简单的方式解决而不使用功能

IS IT NOT POSSIBLE TO SOLVE IN A SIMPLE WAY WITHOUT USING FUNCTION

是的，使用CTE得到结果是可行的：通过使用T-Sql分割长字符串来获取第2和第3个字符串 [ ^ ]

使用Cte是可能的

Using Cte it is possible

DECLARE @table TABLE(Keywords varchar(100))
insert into @table values('SER_ELECTR_003')
;with cte as (
select Keywords,substring(Keywords,0,charindex('_',Keywords))Key1 
	,SUBSTRING(Keywords,charindex('_',Keywords)+1,LEN(Keywords)-charindex('_',Keywords))Key2
from @table
)
select Keywords,Key1,substring(Key2,0,charindex('_',Key2))Key2
	,SUBSTRING(Key2,charindex('_',Key2)+1,LEN(Key2)-charindex('_',Key2))Key3
from cte

这篇关于SQL - 如何基于分隔符拆分字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！