我怎样才能编写一个sql查询来查找一列是另一列的子串的行

时间:2022-05-01 23:05:30

I wish to find all rows in a table where one column is a substring of another column.

我希望在表中找到一列是另一列的子串的所有行。

In other words, suppose I have a table (called people) with two columns: firstname and lastname, and I want to find all people like "rob robinowitz" and "jill bajillion".

换句话说,假设我有一个表(称为people),有两列:firstname和lastname,我想找到所有人喜欢“rob robinowitz”和“jill bajillion”。

Is there a way to do something like "select * from people where lastname like %firstname%"? (But something which actually works).

有没有办法做一些事情,比如“从名字像%firstname%的人那里选择*”? (但实际上有效的东西)。

2 个解决方案

#1


5  

You were close

你很亲密

select * from people where lastname like '%' + firstname + '%'

Alternative way (may be even faster)

替代方式(可能更快)

select * from people where charindex(firstname,lastname)>0

#2


0  

If you are using MySQL you could

如果你使用MySQL,你可以

SELECT * FROM people WHERE INSTR(lastname, firstname) <> 0

#1


5  

You were close

你很亲密

select * from people where lastname like '%' + firstname + '%'

Alternative way (may be even faster)

替代方式(可能更快)

select * from people where charindex(firstname,lastname)>0

#2


0  

If you are using MySQL you could

如果你使用MySQL,你可以

SELECT * FROM people WHERE INSTR(lastname, firstname) <> 0