SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Range on varchar between varchar & numeric
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ismailc
Constraint Violating Yak Guru

South Africa
290 Posts

Posted - 09/13/2013 :  02:23:05  Show Profile  Reply with Quote
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

India
169 Posts

Posted - 09/13/2013 :  02:37:17  Show Profile  Reply with Quote
WHERE CONVERT(VARCHAR,itemcode) BETWEEN 'CL00001' AND '999999'

veeranjaneyulu

Edited by - VeeranjaneyuluAnnapureddy on 09/13/2013 02:38:02
Go to Top of Page

ismailc
Constraint Violating Yak Guru

South Africa
290 Posts

Posted - 09/13/2013 :  02:46:46  Show Profile  Reply with Quote
Thank You but still No Result
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 09/13/2013 :  02:48:06  Show Profile  Reply with Quote
Try this...

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

--
Chandu
Go to Top of Page

ismailc
Constraint Violating Yak Guru

South Africa
290 Posts

Posted - 09/13/2013 :  02:51:59  Show Profile  Reply with Quote
Thank You it returns a Result but how do i know the range/result returned is correct between: CL000001 & 999999
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 09/13/2013 :  03:01:17  Show Profile  Reply with Quote
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

Edited by - bandi on 09/13/2013 03:02:10
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 09/13/2013 :  03:07:18  Show Profile  Reply with Quote
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
Go to Top of Page

ismailc
Constraint Violating Yak Guru

South Africa
290 Posts

Posted - 09/13/2013 :  03:16:17  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 09/13/2013 :  03:27:46  Show Profile  Reply with Quote
You are welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000