SQL Server search and orderby

Question

我想在SQL Server中搜索。当我输入"爆炸"，然后使用'％a bang％'"，然后订购...

我想安排以"爆炸"开头的单词的结果，之后，结果包含"爆炸"，但不开始词。

SELECT *
FROM 
    (SELECT 
         ROW_NUMBER() OVER (ORDER BY CLNTPFX ASC, CLNTCOY ASC,
                                     SURNAME ASC, GIVNAME ASC,
                                     CLNTNUM ASC, UNIQUE_NUMBER DESC) ROWN,
         TEMP1.*
     FROM 
         (SELECT *
          FROM CLNTPF
          WHERE CLNTPFX = 'CN'
            AND CLNTCOY = '9'
            AND SURNAME LIKE 'A Bang'
            AND VALIDFLAG = 1) TEMP1) TEMP2
WHERE 
    ROWN > 12
    AND ROWN <= 24

搜索" O"

时期望的示例

Owner                         
Owner                         
Owner                         
Owner                         
Owner                         
A Owner
A Owner
A Owner                         

你可以帮我吗？

谢谢所有！

Answer 1

假设 c1是您要以 O开头的单词首先出现的单词来对数据进行排序的列，然后跟随文本中的 O的单词，然后是随后的单词所有其他单词，您可以在下面的查询中添加order by。

ORDER BY CASE 
        WHEN c1 LIKE 'O%'
            THEN 1
        WHEN c1 LIKE '%O%'
            THEN 2
        ELSE 3 end;

在您的示例数据上，以下是结果。

C1
------------------------
Owner
Owner
Owner
Owner
A Owner 
A Owner 
A Owner

您可以在此处检查演示