February 2, 2007 at 1:02 pm
I have a two columns in a table with the following data like so:
Food Column Type Column
Apple|Steak|Peanut Fruit|Meat|Nut
Orange|Cashew|Corn|Chicken Fruit|Nut|Vegetable|Meat
Is there a programmatic way to find "Nut" in the Type column and retrieve the corresponding data in the Food column? Wherever "Nut" sits in the Type column is my location to retrieve data in the Food column. Hope this makes sense. Any help would be appreciated.
February 2, 2007 at 1:11 pm
I would suggest creating a numbers table and using a split function if you don't already have one. Search this site for dbo.Split and you should find plenty of posts regarding creating the numbers table as well as the split function. Depending on which version of the split function you find, you will need to make sure that you can pass in the delimiter. What you will end up doing is passing in the Type column with the pipe '|' delimiter and the function will return a table. Go out and grab the numbers table and split function and post back to this thread with more questions.
February 2, 2007 at 1:44 pm
I totally agree with John, but in the mean time, this should work:
DECLARE @food TABLE( Food varchar(35),
Type varchar(35))
INSERT INTO @food
SELECT 'Apple|Steak|Peanut', 'Fruit|Meat|Nut'
UNION ALL
SELECT 'Orange|Cashew|Corn|Chicken', 'Fruit|Nut|Vegetable|Meat'
UNION ALL
SELECT 'Apple|Cashew|Corn|Chicken', 'Fruit|Rock|Vegetable|Meat'
UNION ALL
SELECT 'Lemon|Cashew|Corn|Chicken', 'Fruit|Vegetable|Meat'
SELECT Food FROM @food WHERE Type LIKE '%Nut%'
I wasn't born stupid - I had to study.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply