import string with array into table

Question

import string with array into table

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

dwain.c (10/9/2013)
Jeff Moden (10/9/2013)
Mike Saunders NL (10/7/2013)
Hello again,
I am running into a problem importing a string containing an array into a table.
My string looks like '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'
I want to import this into a temp table that looks like:
ID, Fname, Lname, Sex
1, mark, peters, mr
2, jane, fonda, mrs
3, john,doo,mr
4, james,bond,mr
Can someone help me with this? I really have no clue how to solve this.
Thanks a lot!
Mike
Mike,
Will there always be exactly 2 commas between each set of curly braces?
I sense a solution on after-burners in the air.

Possibly. 🙂

--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: 1004365 More actions · Answer 2

dwain.c (10/9/2013)
And it can't split comma delimited strings as fast as DelimitedSplit8K.

As you said in your fine article, it's only because it was designed to do just one thing but to do that one thing very well. The pattern split you've built is quite handy.

--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)

Dwain Camps SSC Guru Points: 86968 More actions · Answer 3

Jeff Moden (10/9/2013)
dwain.c (10/9/2013)
And it can't split comma delimited strings as fast as DelimitedSplit8K.
As you said in your fine article, it's only because it was designed to do just one thing but to do that one thing very well. The pattern split you've built is quite handy.

Thanks as always for the praise. I can take credit for the idea, but it was ChrisM who came up with the blisteringly-fast code that made it a reality. Hence the fitting attribution of "CM" in the function name.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St