如何使用 SQL 按在单词开头找到且仅在中间找到的搜索短语进行排序

发布于 2024-09-26 11:10:27 字数 327 浏览 9 评论 0原文

假设我有一个带有字段 [Name] 的表 [users]，并且我在应用程序中编写了一个查询来按名称搜索。

数据库中的名字是：

Lala Ally

My SQL 查询是：

SELECT * FROM users WHERE [Name] LIKE '%al%'

其中“al”是我的搜索词。

我如何按开头（即“%al”）和之后的“%al%”中找到的搜索词对结果进行排序。

结果集应该是

Ally 拉拉

我正在使用 SQL SERVER。

谢谢。

原文

Let's say I have table [users] with the field [Name] and I've written a query in my application to search by name.

The names in the database are:

Lala
Ally

My SQL query is:

SELECT * FROM users WHERE [Name] LIKE '%al%'

where 'al' is my search term.

How would I sort the results by search term found in the beginning i.e. '%al' and only afterwards '%al%'.

The result set should be

Ally
Lala

I'm using SQL SERVER.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

扭转时空 2024-10-03 11:10:27

试试这个。假设您使用的是 sql server，这将适用于您需要的所有字符串 -

SELECT * FROM users WHERE [Name] LIKE '%al%'
ORDER BY PATINDEX('%al%',[Name])

Try this. This will work for all strings as you require assuming you are using sql server-

SELECT * FROM users WHERE [Name] LIKE '%al%'
ORDER BY PATINDEX('%al%',[Name])

回复收藏 0 原文

甜妞爱困 2024-10-03 11:10:27

像这样的事情：

SELECT 0, [Name] FROM users WHERE [Name] LIKE 'al%'

UNION

SELECT 1, [Name] FROM users WHERE [Name] LIKE '%al%'
  AND NOT [Name] LIKE 'al%'

ORDER BY
  1, 2

Something like this:

SELECT 0, [Name] FROM users WHERE [Name] LIKE 'al%'

UNION

SELECT 1, [Name] FROM users WHERE [Name] LIKE '%al%'
  AND NOT [Name] LIKE 'al%'

ORDER BY
  1, 2

回复收藏 0 原文

牵强ㄟ 2024-10-03 11:10:27

如果您的 DBMS 支持它，我想您可以将您的选择包装在另一个在名称上使用 index_of 的选择中，例如：

select index_of('al', Name) as sortorder, * from (select * FROM users WHERE [Name] LIKE '%al%')
order by sortorder

If your DBMS supports it, I guess you could wrap your select in another select that uses index_of on the name, such as:

select index_of('al', Name) as sortorder, * from (select * FROM users WHERE [Name] LIKE '%al%')
order by sortorder

回复收藏 0 原文

等风来 2024-10-03 11:10:27

刚刚结束了 SQLite 的这个查询变体做同样的事情，搜索词是“jeff”：

SELECT name
 FROM details WHERE name LIKE 'jeff%' OR name LIKE '%jeff%' 
    ORDER BY (0 - (name LIKE 'jeff%') + (name LIKE '%jeff%')) || name 
    LIMIT 100;

Just ended up with this query variant for SQLite doing same, search term is "jeff":

SELECT name
 FROM details WHERE name LIKE 'jeff%' OR name LIKE '%jeff%' 
    ORDER BY (0 - (name LIKE 'jeff%') + (name LIKE '%jeff%')) || name 
    LIMIT 100;

回复收藏 0 原文

~没有更多了~