CODEWITHSUNDEEP
sqlsql-servert-sql
Pradeep H
Pradeep H

Reputation: 613

SQL query to select with Range condition in source table

Have a scenario to select the value from table where range condition is present in source table.

Like, 

    TableA
    ID  value condition
    1   20      A-M
    2   50      N-Z

    Select value from TableA where condition = 'C%'

--want to select TableA value from TableB by passing person name starts with like,     

-- Here C is item name starts with 
-- Should compare with range (A-M) and return first row.
-- Condition column is varchar(3)

I have seen the solution on other way where range can be compared with input value, but here the range is present in the source table. Please help.

Upvotes: 0

Views: 1552

Answers (4)

Ilyes
Ilyes

Reputation: 14928

I would use QUOTENAME() function as

SELECT *
FROM TableA
WHERE @Condition LIKE QUOTENAME(Condition);

This will be as

WHERE 'C' LIKE [A-M] --return True

Demo1

Demo2

Upvotes: 1

Geovanny Hernandez
Geovanny Hernandez

Reputation: 237

Always you should try to add data and DDL for setup correctly the test scenario, here my proposed solution:

DECLARE @SourceA AS TABLE
(
  ID  INT,
  Value INT,
  Condition VARCHAR(100)
);

INSERT INTO @SourceA (   ID ,
                         Value ,
                         Condition
                     )
VALUES (   1 , -- ID - int
           110 , -- Value - int
           'A-M'  -- Condition - varchar(100)
       ),(2,250,'N-Z')


DECLARE @Alphabet VARCHAR(200)='A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z';


; WITH MyCTE AS
  (
   SELECT ID,Value,Condition, SUBSTRING(@Alphabet, PATINDEX('%'+ LEFT(Condition,1) + '%' ,@Alphabet),(LEN(@Alphabet)-PATINDEX('%'+ RIGHT(Condition,1) + '%' ,@Alphabet))+1) AS FormattedCondition
   FROM @SourceA
  )
  SELECT * FROM MyCTE
  WHERE MyCTE.FormattedCondition LIKE '%C%'

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453028

If I have understood what you are after correctly you can use

SELECT TOP 1 B.*
FROM TableB B
WHERE B.Name LIKE (SELECT CONCAT('[',condition,']%') FROM TableA WHERE ID =1)
ORDER BY B.Id

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If I understand correctly, you should be structuring TableA as:

ID  value Lower  Upper
1   20      A      M
2   50      N      Z

Then you want:

select a.*
from tableA a
where left(@name, 1) between a.lower and a.upper;

You can get this to work with your format, by doing:

select a.*
from tableA a
where left(@name, 1) between left(a.condition) and right(a.condition);

But I don't recommend that. Better to store the condition in two columns.

Upvotes: 2

Related Questions