CODEWITHSUNDEEP
sqlsql-servert-sql
Mahesh
Mahesh

Reputation: 129

how check integer value with db string having integer boundaries in the format of 1 to 10 in sqlserver

I have table like below format, and Condition is age between values in nvarchar format

Id  Condition
1   0 to 10
2   10 to 20
3   20 to 30
4   30 to 40
5   40 to 50
6   50 Above

now I would like to retrieve id value at runtime age need to be check with the table condition column according to the matching boundary.

could you please help on this query?.

Upvotes: 0

Views: 156

Answers (3)

MarkD
MarkD

Reputation: 5316

I agree with SWeko. If you cannot change the types, it seems you are fortunate to have a simple linear progression, so this will work too. NB: THIS IS A NON-OPTIMUM SOLUTION

DECLARE @InputAge TINYINT = 31

;WITH MyTable (Id, Condition) AS
(
-- Corrected age overlaps
    SELECT 1, '0 to 10'     UNION ALL
    SELECT 2, '11 to 20'    UNION ALL
    SELECT 3, '21 to 30'    UNION ALL
    SELECT 4, '31 to 40'    UNION ALL
    SELECT 5, '41 to 50'    UNION ALL
    SELECT 6, '51 Above'
)
SELECT Id
FROM MyTable 
WHERE @InputAge BETWEEN 10 * Id-9 
                AND CASE WHEN Id = 6 THEN 150 ELSE 10*Id END

The string manipulation operation would be something like this. This is also a very poor performer!

DECLARE @InputAge TINYINT = 35

;WITH MyTable (Id, Condition) AS
(
-- Corrected age overlaps
    SELECT 1, '0 to 10'     UNION ALL
    SELECT 2, '11 to 20'    UNION ALL
    SELECT 3, '21 to 30'    UNION ALL
    SELECT 4, '31 to 40'    UNION ALL
    SELECT 5, '41 to 50'    UNION ALL
    SELECT 6, '51 Above'
)
SELECT   Id
        ,LEFT(Condition, CHARINDEX(' ', Condition))
        ,RIGHT(Condition, CHARINDEX(' ', REVERSE(Condition)))
FROM MyTable 
WHERE @InputAge BETWEEN LEFT(Condition, CHARINDEX(' ', Condition)) AND ISNULL(NULLIF(RIGHT(Condition, CHARINDEX(' ', REVERSE(Condition))), 'Above'), 255)

Upvotes: 3

user359135
user359135

Reputation:

You want to restructure that table and use SWekos code, but to answer your question directly:

if it is exactly as you have described there is a relationship between age and range id that you can exploit in a HELLUVA DIRTY HACK:

select 
    CASE p.age
      WHEN  p.age < 70 THEN p.age %10 + 1
      ELSE 6
FROM People as p

;)

Upvotes: 0

SWeko
SWeko

Reputation: 30892

If you have any power over the structure of the tables, change this to a (ID, MaxAge, MinAge) format, and then your query is something like:

select p.*, r.ID as AgeBracket 
from people p
  inner join ageRestrictions r 
    on p.Age between r.MinAge and r.MaxAge

If the structure of the table is set in stone, than mention that in the question.

Upvotes: 2

Related Questions