How to split a string into a table 1

[netsmurph]

I am working on a database designed by someone who was put on my planet to make everyones life more difficult (or maybe he/she wanted to remain employed for life!).

The database is a hash of complex coding to get around poor database design with regards to tables simple rules of normalisation!

The tool analyses files comparing and doing a number of auditing functions. What I am struggling to do is write some sql to see if a file in question has been selected for analysis. The only issue is that rather than have these files as separate records in a table, they are of course all in one record with another file representing which files are selected by the file number and then a ";", i.e. 1;2;4

Can anyone suggest how this can be extracted using a union query in combination with some clever coding?

I know the simple answer is to redesign it but it is too late now as it is used globally!!

Thanks if you can point me anywhere with this.

Andrew

 

[jadams0173]

If I understand the post correctly the values you need are in on cell of the record like 1;2;3;4;5 and you need to break it out to 1 2 3 4 5 as individual values from one record? If I'm on the right track maybe you could use the Split Function.

Sorry if I'm on the wrong track or didn't completely understand the scope of your issue.

I tried to have patience but it took to long! -DW

 

[netsmurph]

Thanks for your reply - I have looked at the split function and understand that i can use it to take my values into a 2D array - can i simply call each value from the array from a query via a function?

If i know the file number i guess i can pass this into a function which can validate if it is included in the array and return a true/false.

Will give this a try, thanks for pointing me in the right direction.

Andrew

 

[Golom]

There is faq701-6293 that provides a SQL-based method for parsing delimited values in a field into separate records.

You can also go the "clever code" route with something like

Select ...
From ...

Where Instr(1, ";" & Replace([FilesSelected]," ","") & ";", ";[red]3[/red];") > 0

That allows you to use the string as-is to find specific values in it. The above for example would return only those records where the string [red]1;2;3;4;5[/red] contained [red]3[/red]. It certainly isn't normalized but it may be a work-around that would be useful until you get around to splitting the values into separate records ... or better yet ... into a separate table.

 

[jadams0173]

Thanks for pointing out that FAQ Golom. I think that along with your tip may be what the OP is looking for. I know I will have a use for it in the very near future. Have a star on me!

I tried to have patience but it took to long! -DW