用逗号分隔时如何获取记录 [英] how to get the records when separated by comma

问题描述

create procedure getdata
@subcomponent varchar(50)
as
begin
 
declare @var varchar(50) =@subcompname
;WITH CTE AS (
    SELECT Sno, Comp, SubComp,
    LEFT(FromValue, PATINDEX('%[0-9]%', FromValue)-1) As FromLetter,
    CAST(RIGHT(FromValue, LEN(FromValue) - (PATINDEX('%[0-9]%', FromValue)-1)) as int) As FromNumber,
    LEFT(ToValue, PATINDEX('%[0-9]', ToValue)-1) As ToLetter,
    CAST(RIGHT(ToValue, LEN(ToValue) - (PATINDEX('%[0-9]%', ToValue)-1)) as int) As ToNumber
    FROM
    (
    SELECT Sno, Comp, SubComp,
       LEFT(SubComp,
          CASE WHEN CHARINDEX(' to ', SubComp) >; 0 THEN
            CHARINDEX('to ', SubComp)-1
          WHEN CHARINDEX(',', SubComp) >; 0 THEN
            CHARINDEX(',', SubComp)-1
          END
       ) FromValue,
       RIGHT(SubComp,
          CASE WHEN CHARINDEX(' to ', SubComp) > 0 THEN
            LEN(SubComp) - (CHARINDEX(' to ', SubComp) + 3)
          WHEN CHARINDEX(',', SubComp) > 0 THEN
            CHARINDEX(',', SubComp)-1
          END
       ) ToValue
    FROM T
    ) InnerQuery
 )
 
 SELECT Sno, Comp, SubComp
 FROM CTE
 WHERE LEFT(@Var, PATINDEX('%[0-9]%', @Var)-1) BETWEEN FromLetter AND ToLetter
 AND CAST(RIGHT(@Var, LEN(@Var) - (PATINDEX('%[0-9]%', @Var)-1)) as int) BETWEEN FromNumber And ToNumber

sno             component         subcomponent               Irnumber
 1                   1            c1 to c100                      001
 2                   1            c200 to c444                    002
 3                   1            c450,c451,c455,c555,c666        003

这里'到'操作工作正常......

当用户输入'c453'时它正在检索第三条记录......我只想在用户输入'c450'或'c451'时才想要第三条记录或'c455'或'c555'或'c666'。 .......





请帮帮我....如何更改查询.....

here 'to' operation is working properly and......
when user enters 'c453' it is retrieving third record......i want third record only when user enters 'c450' or 'c451' or 'c455' or 'c555' or 'c666'. .......


please help me....how to change that query.....

推荐答案

几天前你问过这个问题时，我告诉你这是一种存储数据的愚蠢方式：如何使用sql server中的storedprocedure从字符串中获取子字符串 [ ^ ]



说真的，改变你存储它的方式：插入数据稍微困难一点，但处理起来要容易得多 - 正如你现在所发现的那样。 ..

我知道放弃你编写的代码和有效的设计是很困难的 - 但这是一个好的开发人员的标志，当他犯了一个错误并扔掉时他能意识到一个坏的设计，有利于一个更好的s olution。继续这种方式会一次又一次地给你这个问题......

When you asked this question a couple of days ago, I told you it was a silly way to store the data: how to get substring from string using storedprocedure in sql server[^]

Seriously, change the way you are storing it: it's slightly harder to insert the data, but it's significantly easier to process it - as you are now finding...
I know it's difficult to abandon code you've written and a design that "works" - but it's a sign of a good developer that he can realise when he has made a mistake and throws away a "bad" design in favour of a better solution. Continuing this way will just give you this problem over and over again...

这篇关于用逗号分隔时如何获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！