How to tell if I can convert a varchar to int

Hi,
I am attempting to write a procedure whereby I can get the maximum numeric
value in a varchar column (which may contain a mix of words and number
strings).
I'm trying to use:
SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
end ) FROM table
However, given that entries in the value column can be up to 255 characters,
I obviously have a problem when the number is too long to be cast as an int
(or indeed a bigint or whatever).
In the case of this happening, I'd like to ignore these values, so what I
need is a means of telling whether a value can be cast as an int before
attempting to do it. Using isnumeric(..) isn't enough because it can't
confirm that I can actually convert it.
Any help would be very welcome,
Thanks in advance,
Chris.Maybe try datalength(column) which gives you the number of characters...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>|||To add to Wayne's response, consider using LIKE instead of ISNUMERIC. The
ISNUMERIC function will return 1 for some obscure numeric expressions.
SELECT
MAX(CAST(Value as decimal(38)))
FROM MyTable
WHERE
Value NOT LIKE '%[^0-9]%' AND
DATALENGTH(Value) < 39 AND
Value <> ''
Also, the need to do this sort of thing may be symptomatic of a database
design issue. It's usually not a good idea to store different types of data
in the same column in a relational database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>