Split string in select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Split string in select statement

Author

Topic

ummahajan
Starting Member

2 Posts

Posted - 2014-10-17 : 03:37:13

Hi,

I want to split the string in select statement and display the result

my requirement are as below

declare @unit table
(
unitcode varchar(10),
multipleunit varchar(50)
);

insert into @unit (unitcode,multipleunit) values ('00010','00020,00030,00040,00050')

--Required output

00010 00020
00010 00030
00010 00040
00010 00050

Kindly suggest,how can I query to get the above output.

Thanks

Uday

uday

AAAV
Posting Yak Master

152 Posts

Posted - 2014-10-17 : 09:03:47

(Altered from http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/... didn't have much time to fix the inserts...remote the update and alter you insert to have the firstcolumn filled)
Create FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1) ,
@firstColumn nvarchar(max)
)
RETURNS @output TABLE(firstColumn nvarchar(max),
splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)

update @output set firstcolumn=@firstColumn
END
RETURN
END

select * from (
select * from @unit
cross apply
[fnSplitString](multipleUnit,',',unitcode)
)A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources