Select middle section of an alpha sting to get a numeric Range

Hi,

I have something wrong with my syntax.

I have a char (don't ask me why it is char but it is) column with data being in this format:
10170001
10171030
20170001
40171035
 
I need to select digits  4, 5, 6 to get a range
 eg    xxx700xx to xxx30xx
 
So far I have this but I am getting errors.

select *
from D_Business_Unit
where cast(Substring (BusinessUnitCode,4,3) as numeric,12, 0)) between 700 and 730

Any idea where I am going wrong.

Thanks

Tory
tory10Asked:
Who is Participating?
 
Jan FranekConnect With a Mentor Commented:
OK, so you can just add a condition to your query:

select *
from D_Business_Unit
where Substring (BusinessUnitCode,4,3) like "[0-9][0-9][0-9]"
and convert(numeric(12,0), Substring (BusinessUnitCode,4,3) ) between 700 and 730
0
 
Jan FranekCommented:
Try
select *
from D_Business_Unit
where cast(Substring (BusinessUnitCode,4,3) as numeric(12, 0)) between 700 and 730
0
 
Jan FranekCommented:
Or
select *
from D_Business_Unit
where convert(numeric(12,0), Substring (BusinessUnitCode,4,3) ) between 700 and 730
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
tory10Author Commented:
I have tried these two and I keep getting
'Error converting data type varchar to numeric'
0
 
Jan FranekCommented:
Does  Substring (BusinessUnitCode,4,3) always contain just digits ?You can check it using select * from D_Business_Unit where Substring (BusinessUnitCode,4,3) not like "[0-9][0-9][0-9]" - this should return empty result set.
0
 
tory10Author Commented:
No it doesnt.  When it is a small summary field it is in the formate below:
030MDL            
101BL      
201BV      

However when it is 8 digits long it is always a fully numeric
0
 
tory10Author Commented:
Thanks that worked a treat.  What is the "[0-9][0-9][0-9]" all about.  Do you have any reference I can look up?
0
 
jawad_ashrafCommented:
tory
both of queries suggested by Jan r right,
just copy paste the query.
y dont check if there is any alpha character in this column or non numeric character, i think it is the problem.
Jawad
0
 
Jan FranekCommented:
You may look at http://sybooks.sybase.com/onlinebooks/group-as/asg1251e/blocks/@ebt-link;nh=1;pt=117;lang=cs?target=%25N%15_27017_START_RESTART_N%25;;__next_hit__=5796;DwebQuery=wildcard

It's in fact Sybase ASE documentation, so it's possible, that there will be some small differences from MS SQL. Or try to look up "WILDCARDS" in your MS SQL documentation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.