SQL Find String

Question

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.

where does Description come from?

Stephen
– Stephen

2017-01-06 13:34:42 +00:00
Commented Jan 6, 2017 at 13:34 — Stephen
– Stephen, Commented Jan 6, 2017 at 13:34
it came from the tbl_Foods.

SyntaxError
– SyntaxError

2017-01-06 13:37:47 +00:00
Commented Jan 6, 2017 at 13:37 — SyntaxError
– SyntaxError, Commented Jan 6, 2017 at 13:37
then can you add the structure of tbl_Foods please

Stephen
– Stephen

2017-01-06 13:39:35 +00:00
Commented Jan 6, 2017 at 13:39 — Stephen
– Stephen, Commented Jan 6, 2017 at 13:39
Which DBMS are you using?

user330315
– user330315

2017-01-06 13:42:16 +00:00
Commented Jan 6, 2017 at 13:42 — user330315
– user330315, Commented Jan 6, 2017 at 13:42

MyB · Accepted Answer · 2017-01-06 13:39:28Z

3

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

answered Jan 6, 2017 at 13:39

MyB

1994 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Not that using + for string concatenation is non-standard SQL (and the question is only tagged with sql not a specific DBMS product)

Snowlockk · Accepted Answer · 2017-01-06 13:39:32Z

0

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.

answered Jan 6, 2017 at 13:39

Snowlockk

4512 silver badges7 bronze badges

3 Comments

Sean Lange Over a year ago

This type of splitter is the absolute worst. It is so bad from a performance standpoint that it doesn't even make the list of splitters for comparison among the people who have created and analyzed the different types. Here is a link to a much better splitters than this loop based version. sqlperformance.com/2012/07/t-sql-queries/split-strings Adding the performance challenges here is the multi statement table valued function. These are almost always slower than scalar functions. And of course the looping...

Snowlockk Over a year ago

@SeanLange thanks for the link. Going to update my DBs now that I've seen this.

Sean Lange Over a year ago

Awesome. Glad I could share some knowledge for improvements. :D

Collectives™ on Stack Overflow

SQL Find String

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related