想要将2012-2013年分为两个值，如下所示 [英] want to split 2012-2013 into two values as given below

问题描述

对于我的应用程序，我需要将财政年度一分为二.

我将以2012-2013年为输入，并希望将其输出成两个不同的记录
例如；
2012
2013

for my application i need to split the financial year into two.

i will give 2012-2013 as input and i want to get output into two different records
ex.;
2012
2013

推荐答案

CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

使用它..

to use it..

select id,val from dbo.ParseValues('2012-2013','-')

嗨..

我认为我们不需要任何循环来拆分字符串.我们可以简单地使用XQuery ...
做到这一点.

Hi ..

I think we don''t need any loops to split the string.we can do it simply using XQuery...

--Variable declaration
DECLARE @String As VARCHAR(MAX) 
DECLARE @XmlString AS XML 

--Set the necessary string to split
SET @String = '2012-2013';

--Convert string to XML
SET @XmlString = CAST('<i>'+REPLACE(@String,'-','</i><i>')+'</i>' AS XML);

--XQuery to split the string
SELECT x.i.value('.','VARCHAR(5)') Years FROM
@XmlString.nodes('//i') As x(i)

快乐编码..
Damodara Naidu

Happy Coding..
Damodara Naidu

Below is Split Function in SQL

DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='2011-2012'
SET @Delimiter = '-'
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

这篇关于想要将2012-2013年分为两个值，如下所示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！