[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-03
9
Medium Priority
?
527 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:tory10
  • 5
  • 3
9 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 13918388
Try
select *
from D_Business_Unit
where cast(Substring (BusinessUnitCode,4,3) as numeric(12, 0)) between 700 and 730
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 13918394
Or
select *
from D_Business_Unit
where convert(numeric(12,0), Substring (BusinessUnitCode,4,3) ) between 700 and 730
0
 

Author Comment

by:tory10
ID: 13918428
I have tried these two and I keep getting
'Error converting data type varchar to numeric'
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:Jan Franek
ID: 13918473
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
 

Author Comment

by:tory10
ID: 13918526
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
 
LVL 14

Accepted Solution

by:
Jan Franek earned 500 total points
ID: 13918553
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
 

Author Comment

by:tory10
ID: 13918629
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
 

Expert Comment

by:jawad_ashraf
ID: 13918653
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
 
LVL 14

Expert Comment

by:Jan Franek
ID: 13918995
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question