Check if string could be converted to number

I need to check if string could be converted to a int without throwing any
errors.
I need to do something like this
DECLARE @.s varchar(20)
DECLARE @.i int
--if following is possible
@.i=CAST (@.s as int)
--then
SELECT @.1
--else
SELECT 0
if string is not a number I really don't need to deal with it in the first
place.
The real life example of my scenario is checking uniqueness of check number
for bank transactions. If user writes ATM for check number we don't need to
check the uniqueness.
Could it be done?
Thanks,
Shimon.There's a built-in function in SQL - ISNUMERIC. Look it up in Books Online.
However, it has some issues. They are illustrated here:
http://www.aspfaq.com/show.asp?id=2390
..and more! ;)
ML|||Hi Shimon
you can use ISNUMERIC for this
select isnumeric(@.s)
For eg:
if Value of @.s is '123' then the value returned is 1
if Value of @.s is '123a' then the value returned is 0
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Shimon wrote on Wed, 17 Aug 2005 08:25:30 -0400:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check
> number for bank transactions. If user writes ATM for check number we don't
> need to check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
Try
DECLARE @.s varchar(20)
DECLARE @.i int
/*set value of @.s here*/
SET @.s = 'test'
IF (ISNUMERIC(@.s) = 1)
SET @.i = CAST(@.s as int)
ELSE
SET @.i = 0
SELECT @.i
You'll get a response of 0. Change 'test' to '1000', you'll get 1000.
Dan|||Oh, one thing I missed in my reply - if the string is numeric, but too large
to fit into an int, you'll get an error, so you should have some check on
the string length to determine if it'll fit, or cast into the largest
numeric datatype.
Dan|||Thanks a lot. Exactly what I needed.
Shimon.
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:880E02E2-DA50-4466-9566-FC429802FB37@.microsoft.com...
> Hi Shimon
> you can use ISNUMERIC for this
> select isnumeric(@.s)
> For eg:
> if Value of @.s is '123' then the value returned is 1
> if Value of @.s is '123a' then the value returned is 0
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Shimon Sim" wrote:
>|||Shimon Sim,
Do you think it is enough using "like" operator?
Example:
select
cast(c1 as int)
from
(
select cast('1080' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1081' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('atm' as varchar(10))
union all
select cast('1082' as varchar(10))
) as t1(c1)
where
c1 not like '%[^0-9]%'
AMB
"Shimon Sim" wrote:

> I need to check if string could be converted to a int without throwing any
> errors.
> I need to do something like this
> DECLARE @.s varchar(20)
> DECLARE @.i int
> --if following is possible
> @.i=CAST (@.s as int)
> --then
> SELECT @.1
> --else
> SELECT 0
> if string is not a number I really don't need to deal with it in the first
> place.
> The real life example of my scenario is checking uniqueness of check numbe
r
> for bank transactions. If user writes ATM for check number we don't need t
o
> check the uniqueness.
> Could it be done?
> Thanks,
> Shimon.
>
>|||Thank you for this note.
Shimon.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OnUdojyoFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Oh, one thing I missed in my reply - if the string is numeric, but too
> large to fit into an int, you'll get an error, so you should have some
> check on the string length to determine if it'll fit, or cast into the
> largest numeric datatype.
> Dan
>|||I am not sure if it will work in my scenario.
Thank you
Shimon.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D57DFB0A-33B2-4239-AA77-BA15195B6789@.microsoft.com...
> Shimon Sim,
> Do you think it is enough using "like" operator?
> Example:
> select
> cast(c1 as int)
> from
> (
> select cast('1080' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1081' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('atm' as varchar(10))
> union all
> select cast('1082' as varchar(10))
> ) as t1(c1)
> where
> c1 not like '%[^0-9]%'
>
> AMB
> "Shimon Sim" wrote:
>