How to break string and insert each substring

Question

How to break string and insert each substring

imran-399399

SSC Veteran

Points: 229
More actions
August 12, 2010 at 3:30 am

#387567

Hi All,
I am using Sql server 2005.
My table is like
Empcode Date
em001 2,3,7
em002 12 , 5
I need to break it as
empcode date
em001 2
em001 3
em001 7
em002 12
em002 5
Is there any other way apart from cursor to do the job
Please help

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please search this site for "DelimitedString8K" to find a really fast T-SQL function or have a look at the TallyTable article referenced in my signature.

There is an even fast CLR method available but I don't have the link available right now.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

I went with this: -

--DROP FUNCTION [dbo].[Split]

--GO

--Split function

CREATE FUNCTION dbo.Split (@sep char(1), @s-2 varchar(512))

RETURNS table

AS

RETURN (

WITH Pieces(pn, start, stop) AS (

SELECT 1, 1, CHARINDEX(@sep, @s-2)

UNION ALL

SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s-2, stop + 1)

FROM Pieces

WHERE stop > 0

)

SELECT pn,

SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s

FROM Pieces

)

GO

--Sample data

DECLARE @TABLE AS TABLE(

[Empcode] VARCHAR(5),

[Date] VARCHAR(512))

INSERT INTO @TABLE

SELECT 'em001', '2,3,7'

UNION ALL SELECT 'em002', '12,5'

--Query using split function

SELECT 'em001',

s

FROM [dbo].[Split](',', (SELECT [Date]

FROM @TABLE

WHERE [Empcode] = 'em001'))

UNION ALL

SELECT 'em002',

s

FROM [dbo].[Split](',', (SELECT [Date]

FROM @TABLE

WHERE [Empcode] = 'em002'))

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

The link below will be helpful for these type of problems

http://www.sqlservercentral.com/articles/T-SQL/63003/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

You can split the string on the fly using a Tally table/CROSS APPLY approach

IF NOT OBJECT_ID('tempdb.dbo.#temp', 'U') IS NULL DROP TABLE #temp

SELECT 'em001' AS Empcode, '2,3,7' AS Date INTO #temp

UNION ALL SELECT 'em002', '12,5'

;WITH cteTally (N) AS

(SELECT Number FROM master..spt_values WHERE Type = 'P')

SELECT Empcode, SplitDate FROM #temp

CROSS APPLY

(

SELECT SUBSTRING(Date + ',', N, CHARINDEX(',', Date + ',', N) - N)

FROM cteTally

WHERE N < LEN(Date) + 2 AND SUBSTRING(',' + Date + ',', N, 1) = ','

) AS X (SplitDate)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Post withdrawn. I was speaking out of school and haven't actually tested against that particular split function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

LutzM (8/12/2010)
Please search this site for "DelimitedString8K" to find a really fast T-SQL function or have a look at the TallyTable article referenced in my signature.
There is an even fast CLR method available but I don't have the link available right now.

DelimitedSplit8K?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Houston, we have a winner.... and it's not the Tally Table. The recursive CTE splitter is about twice as fast as the DelimitedSplit8k function on 1000 rows of 800 random numbers with a 1 to 10 digit range.

And, yes, I've verified that it also produces the correct output.

I'm now testing on a smaller set.

I'm pretty much amazed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Houston, we have a different winner. The test table is identical as before except I constrained my test data generator to only 8 values per row. The Tally Table smoked the recursive CTE on the variable population test by almost 3 to 1 and beat the recursive CTE by 2 to 1 on the SELECT/Into test.

I forgot to mention I changed the input variable of the Recursive CTE function to be able to handle a VARCHAR(8000).

Also, on the 3rd test at 80 values per row, the Tally Table again beat the Recursive CTE by nearly 2 to 1 on both the variable population test and the SELECT/INTO test.

So, once again, "It Depends" on what you need.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Heh... oh my... just on a "what if" whim, I just created a new splitter using the Tally Table. It's 3 times faster the DelimitedSplit8K proc and has flatter performance over a much broader range (like ALL of the 8K range). I'm still testing, of course but it blows away the recursive splitter across the entire range of an 8K, as well. It may even resolve the problem when VARCHAR(MAX) comes into play.

I smell one hell of an article coming up. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)