How do I extract only numbers from a field? (Ms Access Sql query)

**Christhemist** · Dec 14th, 2016, 01:59 PM

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?

**Christhemist** · Dec 14th, 2016, 02:25 PM

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;

**si_the_geek** · Dec 14th, 2016, 02:38 PM

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.

**Christhemist** · Dec 14th, 2016, 02:43 PM

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]%'

**Christhemist** · Dec 14th, 2016, 02:45 PM

Input:

1234 Apple Hill Road.

Output:

1234

**techgnome** · Dec 14th, 2016, 03:48 PM

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

**IkkeEnGij** · Dec 14th, 2016, 03:52 PM

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

**Christhemist** · Dec 14th, 2016, 03:56 PM

Some will be like this

Input:

Po Box 1234

Output:

1234

**techgnome** · Dec 14th, 2016, 04:09 PM

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

**Christhemist** · Dec 14th, 2016, 04:24 PM

I basically just want to rip the address number from an address....

**IkkeEnGij** · Dec 14th, 2016, 04:28 PM

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

**techgnome** · Dec 14th, 2016, 05:41 PM

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

**TysonLPrice** · Dec 20th, 2016, 10:26 AM

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

Thread: How do I extract only numbers from a field? (Ms Access Sql query)

Thread Tools

Display

How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Re: How do I extract only numbers from a field? (Ms Access Sql query)

Tags for this Thread

Posting Permissions