SQL scalar variable value between and

Question

'Names' in table 'Data'

"type12pen105A"
"type12pen110A"
"type12pen121B"

Declare @n int;

select Names From Data
where Names ='type12pen'+cast(@n between 100 and 110 as varchar)+'A'

My Required out put is

"type12pen105A"
"type12pen110A"

-1 Essential information is missing and there seems to be no effort in adjusting the question to add that information. *It always amazes me that people expect us to spend our time answering questions they won't spend their time asking. (George)* — Lieven Keersmaekers, Mar 22 '12 at 13:26
Sorry the next minute of posting i add some names to that question plz find them.I should have provide more information.I dont want to take much of your time — gopi nath pandraju, Mar 22 '12 at 13:32
Fair enough, I'll remove the downvote after you've edited the question *(the vote is locked now until someone edits the question)*. — Lieven Keersmaekers, Mar 22 '12 at 13:34

score 1 · Answer 1 · answered Mar 22 '12 at 12:57

1

SELECT Names
FROM Data
WHERE Names LIKE 'type12pen%' 
AND CAST(SUBSTRING(Names,10,3) AS INT) BETWEEN 100 AND 110
AND RIGHT(Names,1) = 'A'

answered Mar 22 '12 at 12:57

Lamak

69,480
12
108
116

score 0 · Answer 2 · answered Mar 22 '12 at 12:58

declare @Data table(
  name varchar(32)
)

insert into @Data values('type12pen105A')
insert into @Data values('type12pen110A')
insert into @Data values('type12pen121B')
insert into @Data values('book11jil124C')


select name
from @Data
where cast(substring(name, 10, 3) as int) between 100 and 110
      and name like 'type12pen%'
      and right(name, 1) = 'A'

If this is a large table you'd probably be better served by running a process on the data and splitting the different aspects of the product name out into individual fields and querying on those. Using substring and right means you won't get the benefit of indexes.

Then you need to modify your question and include more information/examples as to how the name is formatted. — Dave Carlile, Mar 22 '12 at 13:09

score 0 · Accepted Answer · answered Mar 22 '12 at 13:30

Based on the info you provided, this is ugly but it should work:

create table #data
(
    names varchar(50)
)

insert into #data values('type12pen105A')
insert into #data values('type12pen101A')
insert into #data values('type12pen112A')
insert into #data values('type12pen120A')
insert into #data values('type12pen110A')
insert into #data values('type12pen106A')
insert into #data values('type12pen110C')
insert into #data values('type12pen110D')
insert into #data values('type12pen110E')
insert into #data values('type12pen121B')

SELECT Names
FROM #Data
WHERE Names LIKE 'type12pen%' 
AND RIGHT(Names,1) = 'A'
AND replace(replace(names, 'type12pen', ''), 'A', '') BETWEEN 100 AND 110 

drop table #data

results:

type12pen105A
type12pen101A
type12pen110A
type12pen106A

SQL scalar variable value between and

3 Answers3