Data parsing and sorting

[visworking]

I have a table that goes a little like this

User Email Info
bob test@test.com a,b,d,f,g
tim a,d,f,h,z
rob rob@test.com h,z

I need to send to people in charge of a,b,c,d...z what people signed up for that

So I would send Bob and Tim to person in charge of A
And Tim and Rob to H and Z and so on and so forth.

I am using SQL2k and Access 2k. I normally would not have setup the table this way but that is how it is.

 

[dhookom]

I would take the time to normalize the data. Each value in [Info] should create its own record in a related table. You could then use standard sql queries.

Duane
MS Access MVP

 

[visworking]

Yeah thats what I was afraid of. There are about 1000 records with each record containing 10-15 names of things they signed up for.

 

[dhookom]

You could write some code that would loop through your records and extract the individual values to append to the related table. I would never do this manually.

Duane
MS Access MVP

 

[MichaelRed]

... but the one time process is just an update query (into a new table) with parameter(s) run for the collections. It is quite easy to set up, if somewhat tedious to run. Perhaps you could do a procedure for the parameters and test the process on a small subset of your current records and (when satisfied) then run it overnight to create the new table.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Golom]

I agree with the other responders. Restructuring is the preferred solution and it's what you should do if at all possible. If it isn't possible then here's some real nightmare SQL that can handle the problem.

STEP 1: Set up a table of integers containing 1, 2, 3, ... the length of the longest string in your comma-delimited field.

STEP 2: Define the following as a query

SELECT I1.[User], Mid$("," & I1.info & ",", S1.num + 1 , S2.num -
S1.num - 1) AS SubString
FROM MyTable AS I1, Integers AS S1, Integers AS S2
WHERE Mid$("," & I1.info & "," ,S1.num , 1 ) = ','
and Mid$("," & I1.info & "," ,S2.num , 1 ) = ','
and S1.num < S2.num
and InStr(1,Mid$(&quot;,&quot; & I1.info & &quot;,&quot;, S1.num + 1 , S2.num - S1.num - 1), &quot;,&quot=0

That just returns what you should have as a table like

User    SubString

Bob       a
Bob       b
Bob       d
    ...
Tim       a
Tim       d
    etc.

STEP 3: Use that query as though it was the normalized table that you should have.

 

[Golom]

Credit where credit is due. This is modified from code on Rudy Limeback's site at

http://www.r937.com/

Rudy is a truly awesome SQL guru and his handle on this site is r937

 

[Golom]

Minor correction ... the integers table needs to contain values from 1 to the length of the longest string plus 2

 

[visworking]

Ok so if I just throw the code into a SP in Access that should allow me to run it correctly? I have the table created with the values 1-56 (54 is longest)

 

[Golom]

Yep ... that's the theory ... but run the query first just to make sure that I got the cut and paste right. And of course, make the necessary changes to reference the tables and fields correctly.

 

[visworking]

I am getting an error on the save of the SP

ADO Error...Mid$ is not a recognized function name.

 

[dhookom]

SQL Server does not support Mid and Instr. You would either need to create your own Mid and Instr or use SubString and CharIndex.

Duane
MS Access MVP

 

[Golom]

Are you creating a new query, selecting SQL view, pasting the code and saving it? &quot;Mid$&quot; is a standard VB/Script string parsing function. I don't see how it can be undefined.

 

[visworking]

I was just making a new SP in Access, I do not have access currently to query analyzer on the actual SQl server.

Sounds like I will bug some people and get some more things installed.

 

[Golom]

As dhookom suggests, change &quot;Mid$&quot; to &quot;SubString&quot; and change &quot;Instr&quot; to &quot;CharIndex&quot;.

The arguments and sequence are the same for Mid$ and SubString.

When Changing &quot;Instr&quot; to &quot;CharIndex&quot;, remove the &quot;1,&quot; that appears as the first argument.

 

[visworking]

Still no luck, here is the entire SP I am trying to create. I forgot to mention that the Name isnt really a name but an ID number (Int) I have it in a view as a name but its an ID number in that table.

Create Procedure &quot;sp_getEachClub&quot;
SELECT I1.[stuid], Substring(&quot;,&quot; & I1.clubs & &quot;,&quot;, S1.num + 1 , S2.num - S1.num - 1) AS SubString
FROM ClubSurvey AS I1, Integers AS S1, Integers AS S2
WHERE Substring(&quot;,&quot; & I1.clubs & &quot;,&quot; ,S1.num , 1 ) = ','
and Substring(&quot;,&quot; & I1.clubs & &quot;,&quot; ,S2.num , 1 ) = ','
and S1.num < S2.num
and CharIndex(Substring(&quot;,&quot; & I1.clubs & &quot;,&quot;, S1.num + 1 , S2.num - S1.num - 1), &quot;,&quot=0

 

[Golom]

Other than &quot;no luck&quot; ... what's happening. Syntax error? No Records? Wrong Records? The only thing that I can see that might be a problem is that the &quot; As SubString&quot; specification now duplicates the name of a function. You may want to change it to something else or enclose it in [...].

If the values being parsed are to be numbers then use something like

(Cast (Substring(&quot;,&quot; & I1.clubs & &quot;,&quot;, S1.num + 1 , S2.num - S1.num - 1)) As Integer) As [MyInt]

Let me break it down since I don't have SQL Server and can't test myself.

The field being retrieved is all possible substrings of the comma-delimited field. SubStrings that don't qualify are eliminated by the WHERE clause.

The WHERE clause looks for character groupings that start and end with a comma and requires that the first comma be before the second one. It then eliminates those occurrences where there is an embedded comma in the string (i.e. don't include stuff like &quot;a,b&quot; in the result.)

 

[dhookom]

Additional issues when converting Access/Jet syntax to SQL is the use of &quot;&&quot; in Access and &quot;+&quot; in SQL. Also, I would replace all the double-quotes with single-quotes.
Substring(&quot;,&quot; & I1.clubs & &quot;,&quot;, S1.num + 1 , S2.num - S1.num - 1)
should be:
Substring(',' + I1.clubs + ',', S1.num + 1 , S2.num - S1.num - 1)

Duane
MS Access MVP