Extracting data from columns that contain multiple values spearated by a comma

Dev Shed Forums Sponsor:

November 11th, 2004, 06:44 AM

Tryst

Contributing User

Join Date: Oct 2003

Posts: 146

Time spent in forums: 12 h 26 m 49 sec
Reputation Power: 6

Extracting data from a column containing multiple values spearated by a '/' for query

Hi all,

If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...

Code:

Contracts table
--------------------------------
Concode - description
--------------------------------
     KIDD - Kidderminster General  
     UNIV - University Hospitals

and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table

) ...

Code:

controls table
--------------------------
Code - Contracts
--------------------------
   BA - KIDD /UNIV /NWPCT

With the those tables and the columns like they are, how can I get my query to display the following information...

PHP Code:


		
			
---------------------------------------------------

Con(Controls table) - Description(contracts table)

---------------------------------------------------

             BA          - Kidderminster General

             BA          - University Hospitals

---------------------------------------------------

I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table

Tryst

__________________
Tryst

November 11th, 2004, 09:06 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,962

Time spent in forums: 1 Month 4 Weeks 16 h 29 m 38 sec
Reputation Power: 1024

this design violates first normal form (do a search to find out why)

your best option at this point is to redesign the tables

if you cannot, you should at least hunt down the people that designed these tables and shoot them all, slowly

you will not easily be able to do the join with sql

you may be forced to read an entire table into memory, perform an "explode/split/unstring" operation to pull out the individual code values, run additional queries to pull in the code descriptions, and do the matching with code logic in memory

or, you can try -- and it won't be easy -- to "explode/split/unstring" the code values into a temp table, which is more conducive to joining

but if you write the query to do this, then why make it a temp table, why not go back and redesign them permanently

__________________
r937.com | rudy.ca

pre-order my book Simply SQL from Amazon

November 11th, 2004, 10:04 AM

Tryst

Contributing User

Join Date: Oct 2003

Posts: 146

Time spent in forums: 12 h 26 m 49 sec
Reputation Power: 6

Quote:

Originally Posted by r937

this design violates first normal form (do a search to find out why)

Yep, I know the reasons why. It was the first thing I said to my boss when I noticed it

Quote:

Originally Posted by r937

if you cannot, you should at least hunt down the people that designed these tables and shoot them all, slowly

Would love to

Quote:

Originally Posted by r937

ior, you can try -- and it won't be easy -- to "explode/split/unstring" the code values into a temp table, which is more conducive to joining

When you say this, do you mean as in a programming language?

Quote:

Originally Posted by R937

but if you write the query to do this, then why make it a temp table, why not go back and redesign them permanently

Could do, but time is a factor.

I was going to implement (and modify) the follow so that I could perform a loop on each extraction of the foreign key and make a join, then when at the end of the field, just do to the next column. This is all nice and easy in a programming language like PHP and ASP.NET's C#, but bot sure if its at all possible in SQL :-S

Code:

CASE WHEN LEN(Contracts) - 
   LEN(REPLACE(Contracts, '/', '')) > 0 
   THEN LTRIM(SUBSTRING(Contracts, CHARINDEX('/', Contracts) + 1, CHARINDEX('/', Contracts + '/', CHARINDEX('/', Contracts) + 1) - CHARINDEX('/', Contracts) - 1))
ELSE NULL END)

Tryst

November 11th, 2004, 10:27 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,962

Time spent in forums: 1 Month 4 Weeks 16 h 29 m 38 sec
Reputation Power: 1024

does the contracts field contain spaces in addition to the slashes?

November 11th, 2004, 10:43 AM

Tryst

Contributing User

Join Date: Oct 2003

Posts: 146

Time spent in forums: 12 h 26 m 49 sec
Reputation Power: 6

Quote:

Originally Posted by r937

does the contracts field contain spaces in addition to the slashes?

Yes. There could be several spaces between each data value.

Tryst

November 11th, 2004, 01:08 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,962

Time spent in forums: 1 Month 4 Weeks 16 h 29 m 38 sec
Reputation Power: 1024

this works but it will never utilize an index --

Code:

select controls.Code
     , Contracts.description
  from controls
left outer
  join Contracts
    on '/'+replace(controls.Contracts,' ','')+'/'
  like '%/'+Contracts.Concode+'/%'

November 12th, 2004, 02:54 AM

Tryst

Contributing User

Join Date: Oct 2003

Posts: 146

Time spent in forums: 12 h 26 m 49 sec
Reputation Power: 6

Thats awseome stuff.

There is one little problem though,

The query is only returning 14 rows and not 15 (as there are 15 entries in total in the Controls.Contracts column). I have included a screenshot of the table. I think the problem lies in the last two entries in the second row.

Also, another question. How does this query work when on the 'ON' clause you have specified that an entry in the controls.contracts column must begin with a '/' and end with a '/' to mamke a JOIN (taking into consideration the first and last elements in the columns) - or am I understanding the query wrong

Code:

ON '/' + REPLACE(controls.contracts, ' ', '') + '/'

Thanks

Tryst

Attached Images

controls_table.gif (10.6 KB, 183 views)

November 12th, 2004, 06:30 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,962

Time spent in forums: 1 Month 4 Weeks 16 h 29 m 38 sec
Reputation Power: 1024

the missing entry is likely due to the data being missing

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: