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