Range on varchar between varchar & numeric

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 2008 Forums

Transact-SQL (2008)

Range on varchar between varchar & numeric

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2013-09-13 : 02:23:05

Hi, i need help please.
Range on varchar between varchar & numeric
Itemcode Varchar(35)
select * from dbo.Dim_Item where ItemCode = 'CL00001' -- works
select * from dbo.Dim_Item where ItemCode = '999999' -- works

but the moment i want to range:
ItemCode BETWEEN 'CL00001' AND '999999' --NO RESULT

i tried:
where ItemCode >= 'CL00001' AND ItemCode <='999999' --NO RESULT
where CAST(itemcode AS varchar(10)) BETWEEN 'CL00001' AND '999999' --NO RESULT

Please Help

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-13 : 02:37:17

WHERE CONVERT(VARCHAR,itemcode) BETWEEN 'CL00001' AND '999999'

veeranjaneyulu

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2013-09-13 : 02:46:46

Thank You but still No Result

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 02:48:06

Try this...

where ItemCode like '[C-Z][L-Z]%'
OR ItemCode like '[1-9]%'

--
Chandu

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2013-09-13 : 02:51:59

Thank You it returns a Result but how do i know the range/result returned is correct between: CL000001 & 999999

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 03:01:17

Try to understand the logic here...
ItemCode like '[C-Z][L-Z]%' means ItemCode's first letter should be between { C and Z } ; Second letter should be between {L and Z }... Finally you will get the ItemCode which have first 2 letters from CL to ZZ
ItemCode like '[1-9]%' means item code starting with 1, 2, 3, 4 .... up to 9

If you would like to check the result whether correct or not , then change the ranges and check the result..
for example,
where ItemCode like '[C-D][L-Z]%' should return itemcodes starting with CL, CM, CN, .... DA, DB, DC, ....up to DZ

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 03:07:18

Duplicate thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=188178#735784

@ismailc,
Better to delete duplicate threads.. Do not open new threads for same issue...

--
Chandu

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2013-09-13 : 03:16:17

Thank You Very much for sharing your knowledge - i learnt something today :)

Problem now resolved

My apologies for the duplicate, not to sure how to delete.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 03:27:46

You are welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources