在varchar列中选择项目范围(SQL Server) [英] Select range of items in a varchar column (sql server)

问题描述

如何从SQL Server的VARCHAR类型列中选择一系列项目?

How can i select a range of items from a VARCHAR type column in sql server?

我想做类似的事情:

SELECT TE.DESC 
FROM PRODUCT P, ETYPE TE WHERE ( P.IDTYPE = TE.IDTYPE )
AND P.NUMBER BETWEEN '619' AND '623'

"P.NUMBER"列可以包含数字和字母，例如"abc123"，那么sql无法正确选择我想要的内容.

The 'P.NUMBER' column can contain numbers and letters together like 'abc123', then sql can't select correctly what i want.

有什么方法可以做到?

谢谢

推荐答案

首先，您需要创建一个函数，该函数将从NUMBER中删除所有非数字字符并返回浮点数(或整数)，如下所示:

First, you need to create a function which will strip all non-numeric characters from your NUMBER and return a float (or int), like so:

create function dbo.RemoveAlpha(@str varchar(1000))
returns float
AS
begin
    while patindex('%[^0-9]%', @str) > 0
    begin
        set @strText = stuff(@str, patindex('%[^0-9]%', @str), 1, '')
    end
    return convert(float, @str)
end

然后，您可以像这样重写查询:

Then your can rewrite your query like so:

SELECT TE.DESC 
FROM PRODUCT P, ETYPE TE 
WHERE ( P.IDTYPE = TE.IDTYPE )
AND dbo.RemoveAlpha(P.NUMBER) BETWEEN 619 AND 623

这篇关于在varchar列中选择项目范围(SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！