CODEWITHSUNDEEP
sqlsql-server
SyntaxError
SyntaxError

Reputation: 3859

SQL Find String

I would like to know if finding a string can be done from another table. It's a bit complicated.

Here's the table: (tbl_dishes)

| dish                | Type        |
| egg, hotdog & bread | Breakfast   |

From the table above, I want to get the individual descriptions of the column dish from another table

2nd Table (tbl_Foods)

| food   | Description |
| egg    | Fresh       |
| hotdog | red         |
| bread  | toasted     |
| steak  | meat        |

Let's say my query would look like this: (but it's wrong)

  SELECT food, description FROM tbl_Foods 
  WHERE food Exists IN (SELECT dish FROM tbl_Dishes)

My desired results would be:

    | food   | Description |
    | egg    | Fresh       |
    | hotdog | red         |
    | bread  | toasted     |

It's like getting all matched word in the dish column. I don't know if it's possible. Please help.

Thank you.

Upvotes: 2

Views: 97

Answers (2)

Snowlockk
Snowlockk

Reputation: 451

You will need to split the list

DECLARE @DelimString VARCHAR(100)
SET DelimString  = SELECT REPLACE(REPLACE(dish,'&',','),' ', '') FROM tbl_Dishes

DECLARE @Dish TABLE (Dish VARCHAR(50)); INSERT INTO @Dish SELECT CAST(ParamValue AS VARCHAR) FROM MultiValueParams_String(@DelimString)

Use this function.

Create function [dbo].[MultiValueParams_String] (@ParamList varchar(4000))
returns @Values table (RoNum INT,ParamValue varchar(4000)) 
as 
begin
declare @Delim char(1) = ',' -- comma is always the delimiter
declare @Chrind int = 1
declare @Piece nvarchar(50)
declare @RoNum int = 0
while @Chrind>0
    begin 
        select @Chrind=charindex(@Delim,@ParamList)
        if @Chrind>0
            select @Piece=left(@ParamList,@chrind-1)
        else 
            select @Piece=@ParamList
        insert @values(RoNum,ParamValue) values (@RoNum,@Piece)
        select @ParamList = right(@ParamList,len(@ParamList)-@chrind)
        if len(@ParamList)=0 break
        SELECT @RoNum = @RoNum + 1
    end 
return
end

SELECT food, description 
FROM tbl_Foods f
INNER JOIN @Dish d ON f.food = d.dish

Something like this.

Upvotes: 0

MyB
MyB

Reputation: 199

SELECT food, description 
FROM tbl_Foods
join tbl_Dishes 
on tbl_Dishes.dish like ('%' + tbl_Foods.food +'%')

Upvotes: 3

Related Questions