SQL select rows where column contains any of word from string

Question

I have a table with people holidays 'id', 'day', 'month', 'name'

<table rules='all' border='black'>
        <tr>
            <td>id</td>
            <td>day</td>
            <td>month</td>
            <td>name</td>
        </tr>
        <tr>
            <td>1</td>
            <td>1</td>
            <td>5</td>
            <td>Adam</td>
        </tr>
        <tr>
            <td>2</td>
            <td>2</td>
            <td>5</td>
            <td>Pavel a Petr</td>
        </tr>
</table>

I'm saving name from people holidays into strtolower($name) => $name = Pavel a Petr

And I have a table of users where name is Pavel

Now I need SQL query which can select * from users where lower(name) contains '".$name."'

I'm looking for SQL solution. If there is no SQL solution, can some one suggest me a php solution.

Thank you.

can you use select * from users where name like %.$name.% logic? — Rashid, Sep 23 '15 at 08:15
This doesn't work. I need to select if contains any word from string — koca79331, Sep 23 '15 at 08:17

score 3 · Answer 1 · answered Sep 23 '15 at 08:21

Try to use MySQL LIKE Clause

E.g:

"select * from `users` where lower(name) LIKE '%$name%'"

If SQL LIKE clause is used along with % characters, then it will work like a meta character (*) in UNIX while listing out all the files or directories at command prompt.

score 1 · Answer 2 · edited Mar 30 '20 at 15:58

1

You probably want to use LIKE to pattern match.

SELECT * FROM users WHERE lower(name) LIKE '%$name%';

The % is the wildcard operator, so this allows anything before or after the name.

edited Mar 30 '20 at 15:58

Nicolas Gervais

33,817
13
115
143

answered Sep 23 '15 at 08:17

lkjaero

31
6

score 0 · Answer 3 · answered Sep 23 '15 at 08:20

0

you can use REGEXP

SELECT * FROM `users` WHERE LOWER(name) RLIKE  "[[:<:]]$name[[:>:]]"

answered Sep 23 '15 at 08:20

Dyrandz Famador

4,499
5
25
40

I will try, this option I didn't try. – koca79331 Sep 23 '15 at 08:20
Doesn't work...important is $name = Pavel a Petr and name column in user is Pavel – koca79331 Sep 23 '15 at 08:23
@user5366720 Your question is not clear to me. could you include a sample data and your expected output? – Dyrandz Famador Sep 23 '15 at 08:33

score 0 · Answer 4 · answered Sep 23 '15 at 08:30

Maybe it is this you are looking for:

EDIT: You should not have a user with the name "a" :-)

EDIT2: Just found, that the sql-server tag is taken away...

DECLARE @peopleHolidays TABLE(id INT,day INT,month INT, name VARCHAR(100));
INSERT INTO @peopleHolidays VALUES(1,1,5,'Adam'),(2,2,5,'Pavel a Petr'),(2,3,5,'Adam a Max'),(2,4,5,'Max a Petr');

DECLARE @users TABLE(id INT IDENTITY,name VARCHAR(100));
INSERT INTO @users VALUES('Adam'),('Pavel'),('Petr'),('Max');

SELECT *
FROM @peopleHolidays AS pH
CROSS APPLY(SELECT CAST('<r>' + REPLACE(pH.name,' ','</r><r>') + '</r>' AS XML)) AS AsXml(x)
CROSS APPLY
(
    SELECT * FROM @users AS u
    WHERE u.name IN
    (
        SELECT a.b.value('.','varchar(max)')
        FROM AsXml.x.nodes('/r') AS a(b)
    )
) AS UsersInHoliday

score 0 · Answer 5 · edited May 23 '17 at 12:16

0

As other posters have said, LIKE will give you a comparator for part of the string. You can use string concatenation for the like argument and therefore pass the string you're searching for safely and not at as much risk of SQL Injection

select * from users where name like '%' + :name + '%'

Use prepared statements in PHP;

How can I prevent SQL-injection in PHP?

PDO::prepare

edited May 23 '17 at 12:16

Community

1
1

answered Sep 23 '15 at 08:44

Edward Comeau

3,874
2
21
24

score 0 · Answer 6 · answered Sep 25 '15 at 19:42

So I solved this by PHP, not by SQL, code just looks like

function isClientInDb($name, $array){
        global $db;

        $name = strtolower($name);      

        $query = "SELECT * from `clients` where LOWER (name) = '".$name."' and `uid`='".$_SESSION['uid']."'";
        $result = $db->select($query);      
        if(count($result) < 1){

        if(intval(preg_match('/\s/',$name)) > 0){
            if(strpos($name, ",")){
                $pos = strpos($name, ",");      
                $minus = 1; 
            }else{
                if(strpos($name, "a")){
                $pos = strpos($name, "a");  
                $minus = 0;         
                }else{
                $pos = strpos($name, " ");
            }           
            }           
            $pole = array(strtolower(substr($name,0,$pos)), strtolower(substr($name,-($pos+$minus))));

            $sql = "lower(name) = '".$pole[0]."' or lower(name) = '".$pole[1]."'";              
            }else{
            $sql = "lower(name) LIKE '%".$name."%'";            
            }       

        $query = "SELECT * from `clients` where ( $sql ) and `uid`='".$_SESSION['uid']."'";
        $result = $db->select($query);
        if(count($result) < 1){
            return false;           
                        }else{
                            return true;
                        }


        }else{
        return true;        
        }   
        }

SQL select rows where column contains any of word from string

6 Answers6