Searching between two numbers in a VARCHAR column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Searching between two numbers in a VARCHAR column

Author

Topic

kev510
Starting Member

5 Posts

Posted - 2006-07-12 : 09:54:22

Hello.
I made a program which has the "search between" function. It searches about 40 tables, all of which have one column per table with type VARCHAR. I used single quotes in the SQL string to convert the user input values to varchar, but this is where the problem comes in. Lets say one column in a table contains values 2, 3, 9, 100, 200, 300, 9994, A883, ZZ848. If I enter 2 and 999 for the "from/to" search values, I will get a result of "200, 300, 9994", when it should be "2, 3, 9, 100, 200, 300". Please help! Thanks.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-07-12 : 10:12:13

It can be done like this:

RIGHT('0000000000' + YourColumn, 10).
That will put leading zero's on your numbers.

Duane.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 10:13:53

Something like this?

--data
declare @t table (v varchar(100))
insert @t
          select '2, 3, 9, 100, 200, 300, 9994, A883, ZZ848'

--input
declare @from varchar(10)
declare @to varchar(10)

set @from = '2'
set @to = '999'

--calculation
select case when fromI < toI - 2 then substring(v, fromI, toI - 3) end from (
select *
    , patindex('%, ' + @from + '%', ', ' + v + ', ') as fromI
    , patindex('%, ' + @to + '%', ', ' + v + ', ')  as toI
from @t) a

/*results
-------------------------
2, 3, 9, 100, 200, 300
*/

But you really should think seriously about normalising your underlying data structure and redesigning to work from that...

http://www.datamodel.org/NormalizationRules.html
http://www.agiledata.org/essays/dataModeling101.html
http://en.wikipedia.org/wiki/Database_normalization

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 10:20:02

Having just read ditch's response, I realise there could be ambiguity in the question, so maybe something like this instead?

--data
declare @t table (v varchar(10))
insert @t
          select '2'
union all select '3'
union all select '9'
union all select '100'
union all select '200'
union all select '300'
union all select '9994'
union all select 'A883'
union all select 'ZZ848'

--input
declare @from varchar(10)
declare @to varchar(10)

set @from = '2'
set @to = '999'

--calculation
declare @x varchar(100)

select @x = isnull(@x + ', ', '') + v from @t
where replicate('0', 10 - len(v)) + v between replicate('0', 10 - len(@from)) + @from and replicate('0', 10 - len(@to)) + @to

select @x

/*results
-------------------------
2, 3, 9, 100, 200, 300
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources