Question

如何用sql语句模糊查询姓名中有三个词的人名，比如“king george V”,where name like'...'

This is a exercise:
Ships(name,class,launched)
Find the names of all ships whose name consists of three or more words(e.g.,King George V).
select * from Ships where name like '% % %'
三个词,也就是中间有两个空格... 所以在两个空格旁边分别加%
在SQL Server里实验了一下,可以成功...

假如name的值是george King中间好几个空格，那它也会被选择出来,而这个名字不符合要求，所以这种方法不严密

Answer 1

oracle 正则表达式

SQL> select sysdate
2 from dual
3 where regexp_like('king george V',
4 '^[a-zA-z]+[ ]+[a-zA-z]+[ ]+[a-zA-z]+$')
5 ;

SYSDATE
-----------
2009-5-20 上

----------------------------------------------
SQL> select sysdate
2 from dual
3 where regexp_like('king george V',
4 '^[a-zA-z]+[ ]+[a-zA-z]+[ ]+[a-zA-z]+$');

SYSDATE
-----------
2009-5-20 上
------------------------------

Answer 2

select * from Ships where name like '% % %'
三个词,也就是中间有两个空格... 所以在两个空格旁边分别加%
在SQL Server里实验了一下,可以成功...

Answer 3

select * from 表名 where name like "king" or name like "george" or name like "V"

Answer 4

select *
from
Ships
where
name like '%[^ ][ ]%[^ ][ ]%'
and
name not like '%[^ ][ ]%[^ ][ ]%[^ ][ ]%'