Splitting a comma seperated value into multiple columns using SQL View

[supraman3001]

Splitting a comma seperated value into multiple columns using SQL View
I have a table that looks something like this:

Column1 Column2 Column3
John 12, 34, 5,498 Texas
Mike 16, 8, 69, 54 New York
Robert 104, 42, 110 Mexico
Josh 25 Maine


I am trying to seperate the values in Column2 into multiple columns that hold single values so that for instance the first row's Column2 holds each value in a different Column:
Column1 Column2 Column3 Column4
12 34 5 498

Basically I am trying to normalize the data and without doing this, I cannot move forward. I would really appreciate if someone can help me solve this using some sort of SQL Query in Access's SQL View.



Thanks in advance!

[NassauBob]

You are going to have to use a combination of MID() and INSTR() functions, nested, so that they extract the correct substrings from that raw data.

Example: to get the first numeric portion out, you can use MID([Column2],Instr([Column2],1),Instr([Column2],",")-1)

I'll explain. the MID() function takes three arguments: string value to be manipulated ([Column2]), starting position (in the case of the FIRST numeric value, it's the first character), and desired length of the substring (in this case, it won't necessarily always be the same, so you set that value to the position just before the separator; the ",").

Is that clear as mud for ya? If you want a more detailed and thorough explanation, PM me

[RuralGuy]

What do the values in Column2 represent. You may be looking at a normalization issue here.

[NassauBob]

I do too. I was looking at his before values, and wondering if he had commas in table cells. Since I saw no commas between name and the first numeric value, nor last numeric and location, I assumed he was separating columns by spaces.

[supraman3001]

Thanks Shane and sorry about the duplicate post Ruralguy.

To answer your question, Column2 represents all those numbers. As Shane stated, Column1 shows the name, Column2 represents the numbers (which btw are in a "string" data type) and Column3 has the location.

Shane, I will try what you said first thing Monday morning and let you know how things work out. Any other suggestions would be kindly appreciated.

Thanks again!

[supraman3001]

Oh btw, Shane...

Can we use the Instr() in Access 2007 SQL View? I remember getting an error when trying to use that function and also read somewhere that it is not compatible with access.

[RuralGuy]

Other than just random numbers, what does column 2 represent? Are they scores or something?

[supraman3001]

these will be used as the primary key linked with the other table. The other table has these numbers as well 1-XXXX....each number here in column2 basically needs to be linked to its corresponding number in table2.

[supraman3001]

so for instance.
John from Texas is linked to ID 12, 34, 5,and 498 in table2.

There are instances where two different names from table1 are linked to the same ID in table2 as well.

[RuralGuy]

Well I will stop commenting because the design seems very strange. More than ever I feel there is probably a normalization issue that deserves further study and attention. The bottom line is that each time you have to deal with this part of your system, you will end up spending extra time making it work because of the normalization issues. It can certainly be done but it just takes longer.

[NassauBob]

Well let me first say that normalizing your data, which you did say was the reason for this exercise in your first post, is a good idea. As RuralGuy can certainly attest, normalized data will be much easier to bend to your will down the road. But I am preaching to the choir I know, because that is what you said you were doing this for, right? In the opening post, to normalize your data? Cool.

Now. What you can do is make a query that breaks up that comma-separated group into 4 distinct pseudo-columns in query design view. How do you do that? By setting a column up to extract each numeric portion out at a time.

I'm going to PM you about this problem: I have something for you to try.