query problem

Vision G · June 29th, 2008, 12:16 PM

hi, i have a little problem, with a query!
i have the records, example:

th.123
th.156
th.456
th.678
th.wqt

and i want to take in a new field the same records without the "th."
only the numbers!
should i use in a query iif function? and if yes how?

mmcdonal · June 30th, 2008, 06:57 AM

Will there always only be 3 characters to the right of the period in the value? If so, then do something like:

SELECT Right([MyField], 3) As NewNumber FROM ...

If there will be more or less, then you may have to use an array.

If there will always be only two characters and a period to the left of the value you want, then you could also try:

SELECT Right([MyField], (Len([MyField]) - 3)) As NewNumber FROM ...

but this may be tricky.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Old Pedant · June 30th, 2008, 02:22 PM

Much easier than

Code:

SELECT Right([MyField], (Len([MyField]) - 3)) As NewNumber FROM ...

is simply

Code:

SELECT Mid([MyField], 4) As NewNumber FROM ...

But he says he wants to create a NEW FIELD with those values and *ONLY* get those that have numbers after the period.

And I *assume* that the new field will be an integer.

SO....

First, add the new field:

Code:

     ALTER TABLE tablename ADD thNumbersOnly INT NULL

And then use UPDATE to put in all the valid valuse:

Code:

     UPDATE tablename
     SET thNumbersOnly = CLNG( MID( thField, 4 ) )
     WHERE ISNUMERIC( MID( thField, 4 ) )

That's not perfect. If he has a field with a value such as
th.123456789012
then the number there is too large for an integer field and he'd get an error. Or if there is a field with the value
th.1.2E22
then "1.2E22" passes the ISNUMERIC test but of course is way too large to be an INT value.

But I'd bet it would work in a real DB situation.

SerranoG · July 3rd, 2008, 10:16 AM

That last value is not a number, it's text: th.wqt... so that puzzles me. Also, what do you mean a NEW field? Are you storing a calculated value? If the value is always the number part of that th.nnnnn then you should be calculating that each and every time, not storing it.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division