Parsing data to from a field into a new record of a different table

Question

Post reply

Parsing data to from a field into a new record of a different table

emayer

SSC Rookie

Points: 38
More actions
January 28, 2015 at 2:34 pm

#302667

I need to get data from a field and parse it into separate fields of a record in a different table.
The attachment below shows the result of using Jeff Moden's Delimitedsplit8k. the sheet to the right shows what I am trying to get.

Viewing 7 posts - 1 through 6 (of 6 total)

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

emayer SSC Rookie Points: 38 More actions · Answer 1

Forgot to add that the data will be varying lengths and I have control over what the deliminator is

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

See the following for how to use CrossTabs and Pivots.

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

See the following for how to make the CrossTabs dynamic.

http://www.sqlservercentral.com/articles/Crosstab/65048/

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

emayer SSC Rookie Points: 38 More actions · Answer 3

Jeff

Thank You I completely forgot about that, I have been out of SQL for about 8 years.

I thought it would be something simple.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

My pleasure and welcome back!

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

emayer SSC Rookie Points: 38 More actions · Answer 5

Jeff and anyone else looking, I found some VBA code and did this inside of Excel that was link to my DB.

I know that this will make the DBA's cringe a little 😉

Create a VBA function

Function StrSplit(InString, Pos, Delim)

StrArray = Split(InString, Delim)

StrSplit = StrArray(Pos - 1)

End Function

Use

To get the 3rd field from a coma delimited string:

=StrSplit(A1,3,",")

To get the 2nd field from a space delimited string:

=StrSplit(A1,2," ")

Nested example:

To get the 2nd field of a space delimited string nested inside a cama delimed string. Example to get "Doe" out of the following string.

A1=Electrician, John Doe,1234 main st., any town, USA

=StrSplit(TRIM(StrSplit(A3,2,",")),2," ")

NOTE: I know this is an old post, but for anyone else who may have an need. Don't over complicate things, use the KISS method like this.

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

Heh... I congratulate you on your innovation but you export the data to a spreadsheet, write a VB function, manually code the VB function into a spreadsheet once for every element that want to split off, and you call that the "Kiss" method??? :blink:

Post the code that you used for your original post and let's really "Kiss" it.

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