|
Subject:
|
query problem
|
|
Posted By:
|
Vision G
|
Post Date:
|
6/29/2008 12:16:03 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?
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
6/30/2008 6:57:31 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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/30/2008 2:22:09 PM
|
Much easier than
SELECT Right([MyField], (Len([MyField]) - 3)) As NewNumber FROM ...
is simply
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:
ALTER TABLE tablename ADD thNumbersOnly INT NULL And then use UPDATE to put in all the valid valuse:
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.
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
7/3/2008 10:16:08 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
|
