How do I extract only numbers from a field? (Ms Access Sql query)
I am writing a Ms access query to extract only numbers from a field, so that I may use the code in vb.net. How do I do this if at all possible?
Re: How do I extract only numbers from a field? (Ms Access Sql query)
This is an attempt at doing such, but this only return a column of zeros...
Code:
SELECT Mid(Field1, 1, Len(Field1)) Like '%[0-9]%' AS OnlyNumbers
FROM Database;
Re: How do I extract only numbers from a field? (Ms Access Sql query)
Mid(Field1, 1, Len(Field1)) isn't much use, at it is just a very long-winded way of writing Field1
Field1 Like '%[0-9]%' would be a very good thing to put in a Where clause for this (as it will quickly eliminate any rows that don't have numbers in them), but in the Select clause won't do much good (it will return True/False [or 0/1] depending on whether there are any numeric characters in the field).
As to how you should approach this, it depends heavily on the kind of values you have got and exactly what you want to return based on those values.
Re: How do I extract only numbers from a field? (Ms Access Sql query)
so something like this? I need it to return to me only the numbers in that field for each row...
Code:
select * from database1 where Field1 Like '%[0-9]%'
Re: How do I extract only numbers from a field? (Ms Access Sql query)
Input:
1234 Apple Hill Road.
Output:
1234
Re: How do I extract only numbers from a field? (Ms Access Sql query)
Is that truly representative of the data? I ask because, it seems overly simplistic... there's not enough data points for us to make a guess... based on a single line of data, it's as easy as taking the left 4 of the col... but is that truly what the data looks like? Will it always be the first 4?
What about 1234 W. 5th Street ??? your original question asked for ALL numbers in the col... in that case it would be 1235 ... but is that what you want? Or are you after JUST 123? It makes a huge diff... and taking the left 4 doesn't work in this case either... Be careful what you ask for, you'll get it... even if it isn't what you wanted.
-tg
Re: How do I extract only numbers from a field? (Ms Access Sql query)
well, if all are like that (starting with a number)
maybe: ?
select Val(TheField) as TheNumber from TheTable where Val(TheField)<>0 and TheField Is not null
Re: How do I extract only numbers from a field? (Ms Access Sql query)
Some will be like this
Input:
Po Box 1234
Output:
1234
Re: How do I extract only numbers from a field? (Ms Access Sql query)
See? That's what I'm talking about... you add a second data point and all assumptions about the first go right out the window. The larger the sample of imputs & expected outputs are, the more likely you are to get a proper answer.
-tg
Re: How do I extract only numbers from a field? (Ms Access Sql query)
I basically just want to rip the address number from an address....
Re: How do I extract only numbers from a field? (Ms Access Sql query)
yes, that is why it usually is not a good idea to have a single field for a complete adres
you should have separate street,number,place, etc fields , or... live with the consequences
Re: How do I extract only numbers from a field? (Ms Access Sql query)
Quote:
Originally Posted by
IkkeEnGij
yes, that is why it usually is not a good idea to have a single field for a complete adres
you should have separate street,number,place, etc fields , or... live with the consequences
That's debatable ... two lines... two fields... that's all you need... plus one each for City, State, Postal and the +4 (postal extension) ... and possibly country. 7 fields. I've never really seen the reason to beyond that except to make a developer's life hell.
as some one who just built a process to undo all that... .... yeah, I'm not a fan of that.
it "seems" like a good idea but you'd sill have a problem ....
if you have 123 Main St and PO Box 456 ... OK, I get that 123 goes in the Street Number field... but PO Box 456... how does that fit? it doesn't... so now you need another field for that... and then is it just for PO Boxes? What about 123 Main St Ste 987 ???? (which the OP also needs to think about and answer too... what's the expected result in that case?) AND THEN there's ... 123 42nd St Ste 1024
Yeah, breaking up address bits for storage seems like a good idea at times... but I'm not sure it solves anything in this case.
and some addresses have multiple lines ... I better stop there...
bottom line, given all the different types of variations of addresses, and what the OP has stated as a "requirement" - first as "need all numbers or an address" and later as "need the street number" - which conflict with each other and the example given with the PO Box. -- the OP I think needs to look at all of this and decide what it is that is desired and what isn't ...
-tg
Re: How do I extract only numbers from a field? (Ms Access Sql query)
Other comments aside...I was curious myself and found this link with several solution (I didn't try them).
http://stackoverflow.com/questions/1...-from-a-string
