Number only

[Prav]

Hi I have the following records in my table:-

PTR22334
AA0123
HDA/C001
'
E/Y001
FG
Z12345
000000123456
000078910112

How can my query return only 123456 & 78910112 only.

 

[louisjohnphillips]

Hi I have the following records in my table:-

PTR22334
AA0123
HDA/C001
'
E/Y001
FG
Z12345
000000123456
000078910112

How can my query return only 123456 & 78910112 only.

Am I missing the point?

SELECT DesiredField
from MyTable
where left( DesiredField,1) = '0'

or

SELECT DesiredField
from MyTable
where left( DesiredField,4) = '0000'

If leading zeroes are to be eliminated

SELECT CInt( DesiredField )
from MyTable
where left( DesiredField,1) = '0'

 

[Prav]

Cheers

Am I missing the point?

SELECT DesiredField
from MyTable
where left( DesiredField,1) = '0'

or

SELECT DesiredField
from MyTable
where left( DesiredField,4) = '0000'

If leading zeroes are to be eliminated

SELECT CInt( DesiredField )
from MyTable
where left( DesiredField,1) = '0'

 

[John W. Vinson]

Hi I have the following records in my table:-

PTR22334
AA0123
HDA/C001
'
E/Y001
FG
Z12345
000000123456
000078910112

How can my query return only 123456 & 78910112 only.

As an alternative which doesn't depend on leading zeros, use a calculated
field

IsNumeric([fieldname])

with a criterion of True.

The only "gotcha" I can think of is that numbers in scientific notation such
as 3105E2 or 1154D28 will be seen as numeric.