Find an array values in another array

Question

I have 2 strings that are not ordered for example orange is not always at second place in first line or other values.

 apple, orange, water

 water, juice, orange, something, apple

I want TRUE if all first line values exist in second line.

I've tried REGEXP 'apple|orange|water' but | is or not and that it gives me true if one of them exists there not all of them unless I write every possible sort.

I've also tried IN() but it needs 3 statements like :

... 'apple'  IN('water', 'juice', 'orange', 'something', 'apple') AND
    'orange' IN('water', 'juice', 'orange', 'something', 'apple') AND
    'water'  IN('water', 'juice', 'orange', 'something', 'apple') ...

Also tried LIKE but it should be like IN() in making queries.
I tried Match() Against() after all, but it doesn't work in join statement. look at this:
```
SELECT *
FROM t1
INNER JOIN t2
ON
    t1.sth = t2.sth AND
    MATCH(t1.sthelse) AGAINST(t2.sthelse IN BOOLEAN MODE)
```
There's 2 problems here. First, it doesn't work in join (also used where but second problem not solved. Second, AGAINST should be string :-? With Concat() I couldn't do the trick the error exists.

I want to do it not in these hard ways and also for my purpose, I should do it with mysql and cannot access php for manipulating these data. + I can change split character to anything.

Any idea... Appreciated.

I think the `IN()` approach (with one such operation per entry in the first array) is the best solution. — eggyal, Sep 05 '15 at 12:19
I decided to do it by php until there will be a good idea to do that in mysql. — Mahdyfo, Sep 05 '15 at 19:29

score 0 · Answer 1 · edited May 23 '17 at 12:22

My linux VM is still loading so the following code is tested in MS SQL. The pattern matching may break. For instance, your first string is "apple, orange, water" and your second string is "apple pie, orange pie, water bottle".

This is a good reference for converting string to table variable.

How to split string and insert values into table in SQL Server

DECLARE @FirstLineValues varchar(max), @SecondLineValues varchar(max)
DECLARE @eachValue VARCHAR(20)
SELECT @FirstLineValues  = 'abc,3,3,5,6,3', @SecondLineValues = 'abc,2,3,4,5,6,7,8,9'
DECLARE @tblFirstLineValues TABLE(Value varchar(10))
DECLARE @tblSecondLineValues Table (value varchar(10))
-------------------------------------------------------------------------
DECLARE @ind int, @allExist bit
SET @ind = CHARINDEX(',',@FirstLineValues)
WHILE @ind > 0
BEGIN
      SET @eachValue = SUBSTRING(@FirstLineValues, 1, @ind-1)
      SET @FirstLineValues = SUBSTRING(@FirstLineValues, @ind+1, LEN(@FirstLineValues)-@ind)
      INSERT INTO @tblFirstLineValues values (@eachValue)
      SET @ind = CharIndex(',', @FirstLineValues)
END
SET @eachValue = @FirstLineValues
INSERT INTO @tblFirstLineValues values (@eachValue)
-------------------------------------------------------------------------
SET @ind = CHARINDEX(',',@SecondLineValues)
WHILE @ind > 0
BEGIN
      SET @eachValue = SUBSTRING(@SecondLineValues,1,@ind-1)
      SET @SecondLineValues = SUBSTRING(@SecondLineValues,@ind+1,LEN(@SecondLineValues)-@ind)
      INSERT INTO @tblSecondLineValues values (@eachValue)
      SET @ind = CharIndex(',',@SecondLineValues)
END
SET @eachValue = @SecondLineValues
INSERT INTO @tblSecondLineValues values (@eachValue)
-------------------------------------------------------------------------
SELECT @allExist = IIF(count(*) = 0, 1, 0)
FROM @tblFirstLineValues flv 
LEFT OUTER JOIN @tblSecondLineValues slv ON flv.value = slv.value 
WHERE slv.value IS NULL
-------------------------------------------------------------------------
select @allExist 'All first line values exist in second line'

Thank you for answering. Can I do it in join statement? (see question update - the 4th try). And Can u transfer it to a simpler code that I can understand what's going on. Does it do the job by going through all rows ? or it can do everything by one shot? Look, I want to find matches from 2 tables and cannot go through all table 2 rows for a single row in table 1. I wonder if regexp of mysql was better and could do (?=apple|orange|water) in there but it doesn't support it :( — Mahdyfo, Sep 05 '15 at 15:30

Find an array values in another array

1 Answers1