read multiple fields with common delimited data into either a temp table or table variable without using cursors

Question

read multiple fields with common delimited data into either a temp table or table variable without using cursors

mick burden

SSCarpal Tunnel

Points: 4432
More actions
September 24, 2012 at 7:15 am

#282035

if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after
field1 .............. field2................. field3........................... field4
24/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike
25/Sep/2012.......Tony|Steph|Al.....etc
I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field like
jim
frank
sue
Alan
Tom
George
... etc
without using cursors, is this possible?
Sorry for the bad formatting, not sure how to type it in here

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mick burden (9/24/2012)
if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after
field1 .............. field2................. field3........................... field4
24/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike
25/Sep/2012.......Tony|Steph|Al.....etc
I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field like
jim
frank
sue
Alan
Tom
George
... etc
without using cursors, is this possible?
Sorry for the bad formatting, not sure how to type it in here

Can't provide much help with the coding because there is nothing to work with here. If you need help with the actual code you need to look at the first link in my signature about best practices when posting.

To parse your string you should take a look at the article in my signature about splitting strings. The logic found there is what you are ultimately going to need to solve this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

saltpepo Old Hand Points: 396 More actions · Answer 2

Might be u get hel with dis.

CREATE TABLE dbo.Test

(

idint,

create_dt DATETIME NOT NULL,

Field1 NVARCHAR(100) NOT NULL,

Field2 NVARCHAR(2500) NOT NULL

);

GO

Insert Test

Select '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union all

Select '2','25/Sep/2012','jim|frank|sue','jim|frank|sue'

Insert Test

Select '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union all

Select '2','25/Sep/2012','jim|frank|sue','jim|frank|sue'

GO

Declare @asd nvarchar(100)

select @asd=Field1 from Test where create_dt='24/Sep/2012'

Select Data as Field1 from Split(@asd,'|')

CreateFUNCTION [dbo].[Split]

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

Return

END

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

mick burden (9/24/2012)
if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after
field1 .............. field2................. field3........................... field4
24/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike
25/Sep/2012.......Tony|Steph|Al.....etc
I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field like
jim
frank
sue
Alan
Tom
George
... etc
without using cursors, is this possible?
Sorry for the bad formatting, not sure how to type it in here

As Sean points out, the data is a bit vague. Is it something like this?

SELECT field1, field2, field3, field4

FROM (

SELECT

field1 = CAST('24/Sep/2012' AS DATETIME),

field2 = 'jim|frank|sue',

field3 = 'Alan|Tom|George',

field4 = 'Alice|Tony|Mike' UNION ALL

SELECT '25/Sep/2012', 'Tony|Steph|Al', NULL, NULL

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 4

My appologies, I've now created a script to make a table containing the data... I hope this helps

USE test

GO

-- Creating Test Table

CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))

GO

-- Inserting Data into Table

INSERT INTO testtable(field1,field2,field3,field4)

INSERT INTO testtable(field1,field2,field3,field4)

saltpepo Old Hand Points: 396 More actions · Answer 5

Using function u can get desired result without cursors.I created split function above use that.

Declare @asd nvarchar(100)

select @asd=Field1 from Test where create_dt='24/Sep/2012'

Select Data as Field1 from Split(@asd,'|')

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 6

That works well, thank you, but I need it to go across all the fields at once, adding all the data to a single table or recordset

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

saltpepo (9/25/2012)
Using function u can get desired result without cursors.I created split function above use that.
Declare @asd nvarchar(100)
select @asd=Field1 from Test where create_dt='24/Sep/2012'
Select Data as Field1 from Split(@asd,'|')

That function will work but it is not as good for performance. You are using a while loop which is pretty much the same thing as a cursor. Take a look at the solution at the link in my signature about splitting strings. It will blow the doors off the while loop approach for performance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

mick burden (9/25/2012)
That works well, thank you, but I need it to go across all the fields at once, adding all the data to a single table or recordset

So then for the second time in this thread, if you need specific coding help you need to post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. The first link in my signature will help you put that all together. Without this we are just shooting in the dark. Help us help you and you will be rewarded with tested, accurate and fast code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 9

is the following that I put in a thread earlier not good enough? it created a table with the desired data inputted. What I wanted is an output that gave me the following. I wanted the data for 24/Sep/2012

jim

frank

sue

Alan

Tom

George

Alice

Tony

Mike

GO

-- Creating Test Table

CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))

GO

-- Inserting Data into Table

INSERT INTO testtable(field1,field2,field3,field4)

INSERT INTO testtable(field1,field2,field3,field4)

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

mick burden (9/25/2012)
is the following that I put in a thread earlier not good enough? it created a table with the desired data inputted. What I wanted is an output that gave me the following. I wanted the data for 24/Sep/2012
jim
frank
sue
Alan
Tom
George
Alice
Tony
Mike
GO
-- Creating Test Table
CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))
GO
-- Inserting Data into Table
INSERT INTO testtable(field1,field2,field3,field4)
VALUES('24/Sep/2012','jim|frank|sue','Alan|Tom|George','Alice|Tony|Mike')
INSERT INTO testtable(field1,field2,field3,field4)
VALUES('25/Sep/2012','Tony|Steph|Al','Alice|Tony|Mike','Reg|Steve|John')

My apologies, I missed that post. :blush:

Here is one way to do that to get all your data across all the columns.

declare @SearchDate datetime = '20120924'

select item

from TestTable

cross apply dbo.DelimitedSplit8K(field2, '|') f2

where field1 = @SearchDate

union all

select item

from TestTable

cross apply dbo.DelimitedSplit8K(field3, '|') f2

where field1 = @SearchDate

union all

select item

from TestTable

cross apply dbo.DelimitedSplit8K(field4, '|') f2

where field1 = @SearchDate

You can find the most current version of the DelimitedSplit8K function in the article about splitting strings in my signature. Make sure you read the article and understand what this function is doing. Hope this helps.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 11

That's fine Sean, I've done the same myself. As for my problem, although your solution looks good and as it stands would work with my table, I've put the amount of fields as 4 but in reality it stands at 12 and could grow in the future. Is there some kind of dynamic way I can search across the fields?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

If you want something dynamic, you will need dynamic sql code.

Another way to do it would be concatenating the fields (up to 19 with your current length)

select item

from TestTable

cross apply dbo.DelimitedSplit8K(field2 + '|' +

field3 + '|' +

field4 + '|' +

field5 /*+ '|' + fieldN */, '|') f2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2