Substring into table - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Substring into table

Author

Topic

ranalk
Starting Member

49 Posts

Posted - 2014-01-06 : 06:57:15

Hi,

Would appreciate if someone can assist with substring NVARCHAR into table.

Declare @tmp NVARCHAR(MAX) = 'aaa,bbb,ccc,ddd'

I need it as rows in a table like:
aaa
bbb
ccc
ddd

Thanks in advance

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-01-06 : 07:35:56

Use User Defined function called "[dbo].[CustomSplit]" as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create FUNCTION [dbo].[CustomSplit] (@sep char(1), @Words varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(n, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @Words)
UNION ALL
SELECT n + 1, stop + 1, CHARINDEX(@sep, @Words, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT n,
SUBSTRING(@Words, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS Words
FROM Pieces
)

GO
Declare @tmp NVARCHAR(MAX) = 'aaa,bbb,ccc,ddd'
SELECT * FROM dbo.CustomSplit(',', @tmp)

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-06 : 07:39:22

quote:
Originally posted by ranalk

Hi,

Would appreciate if someone can assist with substring NVARCHAR into table.

Declare @tmp NVARCHAR(MAX) = 'aaa,bbb,ccc,ddd'

I need it as rows in a table like:
aaa
bbb
ccc
ddd

Thanks in advance

see
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html
http://visakhm.blogspot.com/2013/01/delimited-string-split-xml-parsing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-01-07 : 07:40:33

If it is always four parts, you can use parsename function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources