Finding SQL substrings

Question

I know Like operator exist for finding sub-strings - and I'm looking for a solution that if exist can save/reduce code lines.

Scenario:

Users table with a column Name representing the full name of users.
- John Doe
- John Spelling
- Rob Lowe
- Rob Williams
API - "..../?name = "Rob Doe"

I can split the string "Rob Doe" by spaces and run two queries:

select * from Users where name like "%Rob%"
select * from Users where name like "%Doe%"

My question - is there in MySql a build in functionality/macro that can do that for me in 1 DB call? instead of 2 or more depending on the number of words in the given string?

why not `select * from Users where name like "%Rob%" or name like "%Doe%"` — splash58, Aug 27 '19 at 15:00
Isn't clear what result you want., Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Aug 27 '19 at 15:02
@splash58 "Rob Doe" was an example of user input from client side and it can be anything - in code (language of your preference) you split the string and loop through the words sending each independently to the query as a variable — Kukula Mula, Aug 27 '19 at 17:46

score 1 · Answer 1 · answered Aug 27 '19 at 15:26

SQL DEMO

With split_parameter as (
    SELECT
      DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ' ', n.digit+1), ' ', -1) val
    FROM
      ( SELECT 'Rob Doe' as vals) as tt1 -- parameter here
      INNER JOIN
      (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
      ON LENGTH(REPLACE(vals, ' ' , '')) <= LENGTH(vals)-n.digit
)
SELECT name, CONCAT('%', val, '%') as filter
FROM users
JOIN split_parameter
  ON users.name like CONCAT('%', val, '%')

OUTPUT

In case you have both matches you may use:

SELECT DISTINCT Name

Nigel Ren · Answer 2 · 2019-08-27T16:00:20.823

Without knowing which database interface your using, it's difficult to do a proper prepared statement version of this code (I've added a mysqli_ version to the end of the answer).

he simple idea would be to explode() the words according to spaces, then build a SQL statement based on splash58's comment. So...

$parts = explode(" ", $input);
$sql = "select * from Users where name like '%".
    implode("%' or name like '%", $parts).
    "%'";
echo $sql;

would give you

select * from Users where name like '%Rob%' or name like '%Doe%' or name like '%fred%'

If using mysqli_, then the following would use prepared statements with as many parts as you need...

$input = 'Rob Doe';

$parts = explode(" ", $input);
// Add %% around the word for the 'like'
array_walk($parts, function(&$part) { $part = "%".$part."%"; });
// Generate the bind types for mysqli_
$types = str_repeat("s", count($parts));

// Build sql with a ? for each word
$sql = "select * from Users where name like ".
    implode(" or name like ", array_fill(0, count($parts), "?"));

$stmt = $conn->prepare( $sql );
// Bind the words to the SQL statement
$stmt->bind_param($types, ...$parts);
$stmt->execute();
// For example - just get an id from the user
$stmt->bind_result($id);

while ( $stmt->fetch() )   {
    echo $id.PHP_EOL;
}

Paul Spiegel · Answer 3 · 2019-08-27T17:57:36.047

You can use a FULLTEXT INDEX like:

CREATE TABLE users (
  id INTEGER,
  name VARCHAR(50),
  FULLTEXT INDEX (name)
);

INSERT INTO users (id, name) VALUES
  ('1', 'John Doe'),
  ('2', 'John Spelling'),
  ('3', 'Rob Lowe'),
  ('4', 'Rob Williams');

Then your query will be simply:

select *
from users
where match(name) against ('Rob Doe')

Result:

| id  | name         |
| --- | ------------ |
| 1   | John Doe     |
| 3   | Rob Lowe     |
| 4   | Rob Williams |

View on DB Fiddle

Another quite simple way is to convert your input string into a regular expression and use RLIKE:

select *
from users
where name rlike replace('Rob Doe', ' ', '|')

This will return the same result, but needs a full table/index scan.

View on DB Fiddle

score -1 · Answer 4 · answered Aug 27 '19 at 15:43

You can split the string and build one single query.

$string = 'Rob Doe';

$query = 'SELECT * FROM Users WHERE 0';
$words = explode($string);
foreach ($words as $word) {
    if (($word = trim($word))) {
        $query .= sprintf(" OR (name LIKE '%s')", mysql_real_escape_string($word)); //Please escape as you want instead of mysql_real_escape_string
    }
}

Finding SQL substrings

4 Answers4