Convert to Number IF it is a number

Pardon my newbieness:
I have a column that could contain numbers or text (not my idea). All
of the values are returned as text. I want to return the value as a
number if it is a number, otherwise (of course) leave it as text. What
can I do?Return the value to where?
A single column in a resultset has exactly one datatype.
Can't your receiving application or front end code figure out what kind of
data it is?
Why are you storing numbers and strings in the same column?
A
"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>|||> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
Use the ISNUMERIC function. Check the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>
A column only has a single datatype so I'm going to assume you require a
result set with two columns: one VARCHAR, one INTEGER. I will also assume
that when you say "text" you really mean "VARCHAR". Doing this using the
TEXT datatype would be a bit more tricky. That's too many assumptions but if
you don't post a more precise spec then assumptions and guesses are what you
will get. You can help us to help you better by taking the advice in the
following article:
http://www.aspfaq.com/etiquette.asp?id=5006
Here's my example:
CREATE TABLE tbl (x VARCHAR(10) PRIMARY KEY) ;
INSERT INTO tbl (x)
SELECT '123' UNION ALL
SELECT '99A' UNION ALL
SELECT 'ABC' ;
SELECT x, CAST(num AS INTEGER) AS num
FROM
(SELECT x,
CASE WHEN x NOT LIKE '%[^0-9]%' THEN x END AS num
FROM tbl) AS T ;
Result:
x num
-- --
123 123
99A NULL
ABC NULL
(3 row(s) affected)
Hope this helps.
David Portas
SQL Server MVP
--|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23R%23NQQKEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Use the ISNUMERIC function. Check the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
Before using ISNUMERIC Tod should be aware of its limitations. Read:
http://www.aspfaq.com/show.asp?id=2390
David Portas
SQL Server MVP
--|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.