SQL Server REPLACE并检查是否存在

Question

我必须在WHERE条件下使用以下场景检查字符串。

存储在数据库中的数据ProductId可以类似

7314-3337有时带有-符号，不以19 为前缀

73143337有时不带符号且前缀不带19

1973143337正确格式

197314-3337有时带有符号

我需要过滤记录ProductId，并且输入的格式正确，即1973143337

WHERE P.ProductId=@ProductId 

如果数据以其他3种格式存储，我该如何过滤？

如果SQL server中不存在字符串replace（-）和前缀19，如何使用？

Answer 1

请检查这2种方法。一个很简单，第二个是一些技巧。（我认为你选择第二个选项，涵盖所有内容）

declare @t table (ProductId varchar(100))
insert into @t
values
('7314-3337')
,('73143337')
,('1973143337')
,('197314-3337')
,('73683337')
,('73143338')
declare @valuetosearch varchar(100) = '1973143337'
--this is very simple , but not work in each schenerio. the second approach is fine.
--select CHARINDEX ( '19','1973143337'), SUBSTRING('1973143337',3,len('1973143337'))
--select * from 
--@t 
--where 
--replace(REPLACE(ProductId ,'-','') ,'19','') = replace(REPLACE(@valuetosearch ,'-','') ,'19','')
select * from 
@t 
where 
REPLACE( case when CHARINDEX ( '19',ProductId) = 1
then SUBSTRING( ProductId ,3,LEN(ProductId))
else ProductId
end ,'-','')
=
REPLACE ( case when CHARINDEX ( '19',@valuetosearch) = 1
then SUBSTRING( @valuetosearch ,3,LEN(@valuetosearch))
else @valuetosearch
end ,'-','')

Answer 2

您应该首先清理数据，如果数据不一致，则无法获得正确的结果。

用于19:预混

UPDATE foo
SET ProductId = '19' + ProductId
WHERE Left(ProductID, 2) <> '19'

用于移除"-"：

UPDATE foo
SET ProductId = REPLACE(ProductId, '-', '')

然后你应该能够得到你想要的结果。

更新：

您可以用单一格式的结果构建一个CTE，然后过滤该CTE:

WITH cte (
FormattedPID
,ProductId
)
AS (
SELECT CASE 
        WHEN LEFT(ProductId, 2) = '19'
            THEN REPLACE(ProductId, '-', '')
        ELSE '19' + REPLACE(ProductId, '-', '')
        END
    ,ProductId
FROM foo
)
SELECT FormattedPID
     ,ProductId
FROM cte
WHERE FormattedPID = @ProductID

Answer 3

您可以确保列的格式正确，如下所示：

1. 通过用空字符串（197314-3337 -> 1973143337, 7314-3337 -> 73143337）替换-来删除它。

2. 在开头添加19（1973143337 -> 191973143337, 73143337 -> 1973143337）。

3. 取结果最右边的10个字符，并与输入（1973143337 -> 1973143337, 1973143337 -> 1973143337）进行比较。

在Transact-SQL:中

WHERE RIGHT('19' + REPLACE(P.ProductId, '-', ''), 10) = @ProductId

当然，这意味着没有索引搜索，因为我们将函数应用于列。

另一种选择是从输入中生成三种非标准格式：

• 切断初始CCD_ 10（1973143337 -> 73143337）；

• 插入-（1973143337 -> 197314-3337）；

• 插入-并切断19（1973143337 -> 197314-3337 -> 7314-3337）。

在Transact-SQL:中

WHERE P.ProductId IN (
  @ProductId,
  SUBSTRING(@ProductId, 3, 999999999),
  STUFF(@ProductId, 7, 0, '-'),
  SUBSTRING(STUFF(@ProductId, 7, 0, '-'), 3, 999999999)
)

这样，如果P.ProductId上有索引，它将被有效地使用。

这两种方法都假定正确格式的长度是固定的。