How to split two words and number between two number?

Question

My table Data looks like

Sno     Componet       Subcomponent    IRNo        

 1         1            C1 to C100      001
 2         1            C101 to C200    002
 3         1            C201 to C300    003

 4         1            C301,C400       004
 5         1            C401,C500       005

If user enter C50 into textbox then it will get the data from First Row.Mean C50 between C1 to C100(C1,C100)

as same as if user enter C340 , then it will the data from SNO 4. Means C340 between C301,C400(C301 to C400)

How can I write the query for this in sql server?

Use `CHARINDEX` and `SUBSTRING`. like here - http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — TarasB, May 03 '15 at 11:59
Better split `Subcomponent` in 3 columns: a char(1) charValue 'C', integer startValue 1, integer endValue 100. Then it's a simple `where charValue= 'C' and 50 between startValue and endValue` — dnoeth, May 03 '15 at 12:04

score 2 · Answer 1 · answered May 03 '15 at 12:30

No comments about the design. One solution for your question is using a CTE to sanitize the range boundaries and get them to a format that you can work with like so:

DECLARE @inputVal varchar(100) = 'C340'

-- sanitize input: 
SELECT @inputVal = RIGHT(@inputVal, (LEN(@inputVal)-1)) 

;WITH cte (Sno, 
          SubcomponentStart, 
          SubcomponentEnd, 
          IRNo
)
AS
(
SELECT
  Sno,
  CASE WHEN Subcomponent LIKE '%to%'
    THEN REPLACE(SUBSTRING(Subcomponent, 2, CHARINDEX('to', Subcomponent)), 'to','')
      ELSE REPLACE(SUBSTRING(Subcomponent, 2,CHARINDEX(',', Subcomponent)), ',','')
    END as SubcomponentStart,    
  CASE WHEN Subcomponent LIKE '%to%'
    THEN REPLACE(SUBSTRING(Subcomponent, CHARINDEX('to', Subcomponent)+4, LEN(Subcomponent)), 'to', '')
      ELSE REPLACE(SUBSTRING(Subcomponent, CHARINDEX(',', Subcomponent)+3, LEN(Subcomponent)), ',', '')
    END as SubcomponentEnd,
   IRNo 
from test
)
SELECT t.*
FROM test t
INNER JOIN cte c
ON t.Sno = c.Sno
WHERE CAST(@inputVal as int) BETWEEN CAST(c.SubcomponentStart as INT) AND CAST(c.SubcomponentEnd as INT)

SQL Fiddle / tested here: http://sqlfiddle.com/#!6/1b9f0/19

if there are more delimiters (i.e: dash "-" like "C501 - C600") then a new case statement scenario can be added. — Eduard Uta, May 03 '15 at 12:32

score 2 · Accepted Answer · edited May 23 '17 at 12:28

This is a terrible design and should be replaced with a better one if possible. If re-designing is not possible then this answer by Eduard Uta is a good one, but still has one drawback compared to my suggested solution:
It assumes that the Subcomponent will always contain exactly one letter and a number, and that the range specified in the table has the same letter in both sides. a range like AB1 to AC100 might be possible (at least I don't think there's a way to prevent it using pure t-sql).
This is the only reason I present my solution as well. Eduard already got my vote up.

DECLARE @Var varchar(50) = 'C50'
-- also try 'AB150' and 'C332'

;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

sqlfiddle here

score 0 · Answer 3 · answered May 03 '15 at 12:23

For example you're getting UserEntry in variable @UserEntry, entry value is 'C5'.

-- Start From Here --

set @UserEntry = substring(@UserEntry,2,len(@UserEntry)-1)

select * from <tablename> where convert(int,@UserEntry)>=convert(int,SUBSTRING(Subcomponent,2,charindex('to',Subcomponent,1)-2)) and convert(int,@UserEntry)<=convert(int,(SUBSTRING(Subcomponent,charindex('c',Subcomponent,2)+1,len(Subcomponent)-charindex('c',Subcomponent,3))))

How to split two words and number between two number?

3 Answers3