SELECT WHERE NOT A NUMBER/IS A NUMBER?
Ciaran
number or that don't contain a number?
Thanks,
Ciarán
strawberry
Can you provide an example?
Ciaran
Sorry reading that again now, it's unusually phrased and is quite
confusing!
I'm really asking two questions:
1. how to select only records that contain a number in a varchar
column
2. how to select only records that DONT contain a number in a varchar
column
something like:
SELECT * FROM table WHERE ISNUMBER(myVarCharColumn)
SELECT * FROM table WHERE ISSTRING(myVarCharColumn)
Hopefully this makes more sense!
Ciarán
strawberry
No, I meant an example of the data you're trying to work with.
Erick T. Barkhuis
> something like:
> SELECT * FROM table WHERE ISNUMBER(myVarCharColumn)
> SELECT * FROM table WHERE ISSTRING(myVarCharColumn)
You probably want to make use of simple regular expressions, like
^[0-9]+$ for a string that only consists of 1-n digits
Examples? See:
> <http://www.brainbell.com/tutorials/MySQL/Using_MySQL_Regular_Expressions.htm>
--
Erick
Gordon Burditt
>> > number or that don't contain a number?
>> > Thanks,
>> > Ciarán
>>
>> Can you provide an example?
>
>
>Sorry reading that again now, it's unusually phrased and is quite
>confusing!
>I'm really asking two questions:
>1. how to select only records that contain a number in a varchar
>column
>2. how to select only records that DONT contain a number in a varchar
>column
>
>something like:
>SELECT * FROM table WHERE ISNUMBER(myVarCharColumn)
>SELECT * FROM table WHERE ISSTRING(myVarCharColumn)
>
>Hopefully this makes more sense!
Not really. You might make use of the regular expression matching
capabilities of MySQL but you also need to decide what you mean
by "a number".
What about stuff that contains a number and a non-number BOTH?
Classify the following inputs the way you want them:
"384 dead weasels"
"eight"
"1.2, 37.8, 22"
"-123.47e+8"
"I ate five tacos"
"I ate 5 tacos"
"I 8 5 tacos"
"19.2.34.86"
"1,348"
"+568"
ThanksButNo
> Classify the following inputs the way you want them:
> "384 dead weasels"
> "eight"
> "1.2, 37.8, 22"
> "-123.47e+8"
> "I ate five tacos"
> "I ate 5 tacos"
> "I 8 5 tacos"
> "19.2.34.86"
> "1,348"
> "+568"
"27.50-30.00" which happens to equal "(2.5)"
\:-\
Ciaran
Oh, I just mean the most basic circumstance where one value might be
17 and the next might be "dog". In other words, the entire field is
either a number or a string.
Cheers,
Ciarán
Ciaran
wrote:
>
> --
> Erick
Hey Thanks Erick, looks like this solved the problem. The my solution
is posted below for anyone interested:
Matches fields containing only numbers:
SELECT * FROM table WHERE columnName REGEXP '^[0-9]+$'
Matches fields containing only letters:
SELECT * FROM table WHERE columnName NOT REGEXP '^[0-9]+$'
I also had partial success including decimal points in the expression
like so: ^[0-9]\\.+$
Hopefully this thread helps someone else out - there's nothing much
like this out there at the moment.
Ciarán
strawberry
Well then it's easy:
SELECT * FROM tester WHERE test_string > 0;
GL
no matter, REGEX does the job with the anchors ^ and $ :
^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$
or better :
^[-+]?[[:digit:]]*\.?[[:digit:]]+([eE][-+]?[[:digit]]+)?$
SELECT * FROM Table WHERE Field REGEX ^[-+]?[[:digit:]]*\.?[[:digit:]]+([eE][-+]?[[:digit]]+)?$
matches every floating point number (optionally with mantissa and exponent)...
ThanksButNo
Kees Nuyt
No.
>That's a number, right?
No, that's a formatted string of digits, parentheses and hyphens,
without strict numeric meaning.
If you take care of parentheses and hyphens in the application
and store the number as 8005551212, yes, then it's a number.
I would advice to always add the international prefix to phone
numbers you store in a database.
>8-D
--
) Kees
(
c[_] As easy as 3.14159265358979323846264338327950288419716
-- [#12]
Michael Austin
No - that is TEXT, CHAR or VARCHAR.
>
> 8-D
ThanksButNo
Well of course it is. The whole point of the original
exercise was to read a char or varchar column and make
the determination, "Is it a number?"
E.g., for a value of "fred" the answer is No.
For a value of "19.5" the answer is Yes.
Where it gets tricky is the semantics.
Should "seventeen" be Yes or No? Depends on what you want
and what you need for your app. E.g., a check writing
application may need "numbers" such that it can print out:
"three hundred forty two dollars and five cents". Sure it's
stored in a varchar, but semantically, it's a "number".
Personally, I think "(800)555-1212" should be a number too.
Just my "too cents".
