我使用的是一个存储过程,它将策略编号作为参数varchar。当我们从表中检索时,我需要删除策略编号的最后4个字符。但政策数字的数据并不一致,所以我很困惑如何使用这种逻辑。样本保单编号为:
- KSDRE0021-000
- APDRE-10-21-000
- KSDRE0021
- APDRE-10-21
我们的表格中有四种形式的政策。对于一些政策来说,没有"-1000"的尾端,所以这是具有挑战性的部分。现在,当我从表中检索数据时,我需要从策略中消除尾随部分"-000"。
这是示例代码,它从表中提取策略数据。
Create Proc usp.dbo.policydataSP @policy_num varchar(18)
AS
Begin
Select * from policy_table pt
where pt.policy_num = @policy_num
End
步骤1:创建一个用户定义函数以规范化策略编号。
create function dbo.normalize_policy_num
(@policy_num varchar(100))
returns varchar(100)
as
begin
-- replace trailing digits
if (@policy_num like '%-[0-9][0-9][0-9]')
set @policy_num = left(@policy_num, len(@policy_num) - 4)
-- replace remaining hyphens
set @policy_num = replace(@policy_num, '-', '')
return @policy_num
end
这样做的实质是从包含模式的策略编号中去掉尾随的"-000",然后删除剩余的连字符。此功能似乎适用于您提供的保单号码:
-- returns: KSDRE0021
select dbo.normalize_policy_num('KSDRE0021-000')
-- returns: APDRE1021
select dbo.normalize_policy_num('APDRE-10-21-000')
-- returns: KSDRE0021
select dbo.normalize_policy_num('KSDRE0021')
-- returns: APDRE1021
select dbo.normalize_policy_num('APDRE-10-21')
第2步:按如下方式修改SP:
create proc usp.dbo.policydataSP
@policy_num varchar(18)
as
begin
select
dbo.normalize_policy_num(pt.policy_num) as normalized_policy_num,
pt.*
from policy_table pt
where dbo.normalize_policy_num(@policy_num) = dbo.normalize_policy_num(pt.policy_num)
注意:如果您能够修改表模式,您可以使用上面指定的UDF添加一个持久化的计算列。如果向其中添加索引,查询将运行得更快。然而,插入会有一些惩罚,因此需要进行权衡。
这可能是您的最佳选择。将策略编号与请求参数的长度匹配:
Create Proc usp.dbo.policydataSP
@policy_num varchar(18)
AS
Begin
Select * from policy_table pt where LEFT(len(@policy_num),pt.policy_num) = @policy_num
End
如果您只想在返回结果时剥离-000
:
select case right(policy_num, 4)
when '-000' then left(policy_num, len(policy_num) - 4)
else policy_num end as policy_num
from policy_table pt
where pt.policy_num = @policy_num
如果您想去掉破折号后面的任何三位数:
select case when policy_num like '%-[0-9][0-9][0-9]' then left(policy_num, len(policy_num) - 4)
else policy_num end as policy_num
from policy_table pt
where pt.policy_num = @policy_num