SQL While循环使用substring来获得第一个匹配 [英] SQL While loop with substring to get the first match

问题描述

您好，



我需要一些帮助才能从sql中获取结果。我有一个10位数值，我在while循环中使用，并希望它从我选择的表中带回第一个正确的匹配。此代码带回正确的数据但同时显示多个项目。

Hi There,

I need some help to get a result back from sql. I have a 10 digit value that I am using in a while loop and want it to bring back the first correct match from the tables I am selecting from. this code is bringing back the correct data but it displays multiple items at the same time.

ALTER proc data_ee_test_fk
            as
            DECLARE @cli varchar(10)
            SET @cli = '123456789'
            WHILE len(@cli) > 0
            begin
            if exists(
            select distinct * from data_ee_dials
            where substring(dialprefix,1,Len(@cli)) = @cli)
            SELECT DISTINCT * from Data_EE_Codes a left join
            dbo.Data_EE_Dials b on a.CallType = B.ChargeCategory
            where substring(dialprefix,1,Len(@cli)) = @cli

            --select min( dialprefix1 ) from data_ee_dials  where substring(dialprefix,1,Len(@cli)) = @cli
            SET @cli = left(@cli,LEN(@cli)-1)
            print @cli
            end

没有第二个选择它永远循环。



请帮助：）

without the second select it loops forever.

Please help :)

推荐答案

你没有说清楚，但我认为问题是你是从一个10个字符的候选前缀开始，如果没有匹配你需要扩大搜索范围国王搜索前缀不太具体。找到前缀之后，您需要找到使用该前缀的第一个代码。如果这不是你想要达到的目的，请重写问题。

You don't make it clear but I assume that the problem is that you are starting with a 10 character candidate prefix and if nothing matches you need to widen the search by making the search prefix less specific. Having found a prefix you then want to find the first code that uses that prefix. If this isn't what you're trying to achieve please rewrite the question.

declare @done      bit
set     @done      = 0
declare @searchCLI varchar(10)
set     @searchCLI = '123456789'

-- Start narrow and then broaden the search until we find
-- a known CLI or give up.
while @done=0 and len(@searchCLI) > 0

  if exists(select dialprefix from Data_EE_Dials where dialprefix like @searchCLI)
  begin
    set @done = 1
  end

  if @done = 0
  begin
    set @searchCLI = substring(@searchCLI,1,len(@searchCLI)-1)
  end

end

-- We've either got a known CLI or a zero length string.
-- Return the first matching record if there is one.
select top 1 *
  from Data_EE_Codes as Codes
    inner join Data_EE_Dials as Dials
          on   Dials.ChargeCategory = Codes.CallType
  where dials.dialprefix like @searchCLI

这篇关于SQL While循环使用substring来获得第一个匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！