SQL从字符串中选择包含任意单词的列 - SQL select rows where column contains any of word from string

SQL select rows where column contains any of word from string

我有一个包含假日'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>

我把人名从people holidays保存到strtolower($name) => $name = Pavel a Petr

我有一个用户表，名字是Pavel

现在我需要SQL查询，可以select * from users where lower(name) contains '".$name."'

我正在寻找SQL解决方案。如果没有SQL解决方案，有人可以建议我一个php解决方案。

谢谢。

尝试使用MySQL LIKE子句

例句:

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

如果SQL LIKE子句与%字符一起使用，那么它将像UNIX中的元字符(*)一样工作，同时在命令提示符中列出所有文件或目录

您可能想使用LIKE进行模式匹配。

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

%是通配符操作符，因此允许在名称之前或之后的任何内容。

你可以使用REGEXP

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

也许这就是你正在寻找的:

编辑:你不应该有一个名为"a"的用户:-)

EDIT2:刚刚发现，sql-server标签被拿走了…

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

正如其他海报所说，LIKE将为部分字符串提供比较器。对于类似的参数，你可以使用字符串连接这样就可以安全地传递你正在寻找的字符串，而不会有太多SQL注入的风险

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

在PHP中使用预处理语句

如何在PHP中防止sql注入?

PDO:准备

我用PHP解决了这个问题，而不是SQL，代码看起来就像

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;        
        }   
        }