• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Split in SQL Server

I have values in a variable(term) as (2004,2005,2006,....2011). I would like to split the term into individual year and store in another variable say X.

Would you please help me with this. Thanks.
0
cimscims
Asked:
cimscims
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
use this functions, then

select dbo.splitNGet('2004,2005,2006,2011',3,',')

to get the third element...
CREATE FUNCTION [dbo].[fnSplit](    
@sInputList VARCHAR(8000), -- List of delimited items   
@sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (n int, item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000);
DECLARE @sRow int = 1;

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 @sRow, @sItem 
		set @sRow = @sRow + 1;  
	END
	IF LEN(@sInputList) > 0 INSERT INTO @List SELECT @sRow, @sInputList -- Put the last item in
	RETURN
END;

CREATE function [dbo].[splitNGet](
@sInputList VARCHAR(8000), -- List of delimited items 
@n int,  
@sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
)RETURNS varchar(8000)
begin
	declare @item varchar(8000);

	select @item = item from dbo.fnSplit(@sInputList, @sDelimiter)
	where n=@n;

	return @item;
end;

Open in new window

0
 
HainKurtSr. System AnalystCommented:
or you can use

select * from dbo.fnSplit('2004,2005,2006,2011',',')

1      2004
2      2005
3      2006
4      2011

select dbo.splitNGet('2004,2005,2006,2011',3,',')

2006
0
 
cimscimsAuthor Commented:
It worked.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now