sql查询返回与搜索关键字匹配的结果，但也检索按字母顺序排列在搜索关键字后面的记录

Question

标题很难解释。

我以前有一个网络服务，允许用户输入搜索关键字，例如4，它会返回以该搜索关键字开头的记录例如4、4a、4b。

现在我被要求实现一个解决方案，如果用户键入4，它将返回结果：

4
4a
4aa
4b
5
5a
5b
5c
5Z
6
52.
etc.. 

50条记录

我不知道从哪里开始更改查询。。。被搜索的col是字母数字，这是令人困惑的地方。

查询本身超过20行，但将结果链接到搜索关键字的部分只是；

(col LIKE @searchKey + '[a-zA-Z.]%' OR col = @searchKey OR col >= @searchKey)

根据要求，订单为：

ORDER BY   
        CASE WHEN col LIKE '[a-zA-Z]%' Then 1 ELSE 0 END,
                 CAST(SUBSTRING(col, 0, 
                  CASE WHEN patindex('%[a-zA-Z.]%', col) = 0
                  THEN len(col)+1
                  ELSE patindex('%[a-zA-Z.]%', col)
                  END) 
                 as INT),
    CASE WHEN col LIKE '[a-zA-Z]%' THEN SUBSTRING(col, 1,1) END,
    CASE WHEN col LIKE '[a-zA-Z]%' AND len(col) = 1 THEN 0 ELSE 1 END,
    CASE WHEN col LIKE '[a-zA-Z]%' AND len(col) = 2 THEN 0 ELSE 1 END,
    CASE WHEN col LIKE '[a-zA-Z]%' AND len(col) = 3 THEN 0 ELSE 1 END,
    col;

例如，现在如果我通过82，我就会得到结果：

8
8A
8B
8E
8H
9
9A
9C
9D
9E
82
82A
82B
82C
.
.
.
.
99R

然而，我需要的是，如果用户键入82，他们不会收到8、8x、9、9x等结果。

Answer 1

您需要将数字部分与字母部分分离，然后将它们重新组合为一个新的数值，该数值可以进行排序和筛选。您需要对用户输入执行相同的操作。

请注意，这并不能处理你在帖子中提到的时期，因为你没有告诉我们应该如何处理。如果它不相关，那么使用replace函数来消除它。此外，这不会处理字符串的alpha部分超过一个字符的情况。

我假设用户可以在数字输入后输入一个字符。如果不是这样的话，这可以通过不必转换用户输入和不必将数字部分乘以大数字来简化。

100000应该大于字符串数字部分中可能的最大数字。根据需要添加额外的零。

SQL Fiddle

declare @t table (col varchar(4))
declare @userinput varchar(4)
set @userinput = '9C'
insert into @t values ('8')
insert into @t values ('8A')
insert into @t values ('8B')
insert into @t values ('8E')
insert into @t values ('8H')
insert into @t values ('9')
insert into @t values ('9A')
insert into @t values ('9C')
insert into @t values ('9D')
insert into @t values ('9E')
insert into @t values ('82')
insert into @t values ('82A')
insert into @t values ('82B')
insert into @t values ('82C')
insert into @t values ('99R')
;with cte as (
    select 
        col,
        CONVERT(int,
            case when PATINDEX('%[a-z]%', col) = 0 
                then col
                else LEFT(col, PATINDEX('%[a-z]%', col) - 1)
            end
        ) * 100000
        +
        ascii(case when PATINDEX('%[a-z]%', col) = 0
            then ' '
            else SUBSTRING(col, PATINDEX('%[a-z]%', col), len(col))
        end) newcol
    from @t
)
select *
from cte
where 
    newcol >= (
        CONVERT(int,
            case when PATINDEX('%[a-z]%', @userinput) = 0 
                then @userinput
                else LEFT(@userinput, PATINDEX('%[a-z]%', @userinput) - 1)
            end
        ) * 100000
        +
        ascii(case when PATINDEX('%[a-z]%', @userinput) = 0
            then ' '
            else SUBSTRING(@userinput, PATINDEX('%[a-z]%', @userinput), len(@userinput))
        end)
    )
order by newcol

Answer 2

如果您想在搜索键后面按字母顺序排列，请使用>=:

where col >= @searchKey