FIND OUT IT IS A NUMBER OR STRINg

I have a column with varchar type..
It has some records like this...
isbuluyorum
11222
a1285
12r23
How can i find out it is a number or string ?Savas Ates wrote:
> I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
SELECT isbuluyorum AS is_numeric
FROM your_table
WHERE isbuluyorum NOT LIKE '%[^0-9]%' ;
SELECT isbuluyorum AS is_alpha
FROM your_table
WHERE isbuluyorum LIKE '%[^0-9]%' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||table name =savo
column name=sal type=CHAr
records
sal
saalasdad
12da
122
dasd212
I applied
SELECT sal AS is_numeric
FROM savo
WHERE sal NOT LIKE '%[^0-9]%' ;
SELECT sal AS is_alpha
FROM savo
WHERE sal LIKE '%[^0-9]%' ;
It returned
is_alpha is_numeric
saalasdad Nothing
12da
122
dasd212
"Savas Ates" <in da club>, haber iletisinde unlar
yazd:%23sVx3MBJGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
>|||SELECT sal, isnumeric(sal)
FROM savo
"Savas Ates" <in da club> wrote in message
news:eyFEMjBJGHA.3752@.TK2MSFTNGP11.phx.gbl...
> table name =savo
> column name=sal type=CHAr
> records
> sal
> saalasdad
> 12da
> 122
> dasd212
> I applied
> SELECT sal AS is_numeric
> FROM savo
> WHERE sal NOT LIKE '%[^0-9]%' ;
> SELECT sal AS is_alpha
> FROM savo
> WHERE sal LIKE '%[^0-9]%' ;
> It returned
> is_alpha is_numeric
> saalasdad Nothing
> 12da
> 122
> dasd212
>
>
> "Savas Ates" <in da club>, haber iletisinde unlar
> yazd:%23sVx3MBJGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||Savas Ates wrote:
> I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
http://www.aspfaq.com/show.asp?id=2390
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Sat, 28 Jan 2006 16:40:28 +0200, "Savas Ates" <in da club> wrote:

>table name =savo
>column name=sal type=CHAr
>records
>sal
>saalasdad
>12da
>122
>dasd212
>I applied
>SELECT sal AS is_numeric
> FROM savo
> WHERE sal NOT LIKE '%[^0-9]%' ;
>SELECT sal AS is_alpha
> FROM savo
> WHERE sal LIKE '%[^0-9]%' ;
>It returned
> is_alpha is_numeric
>saalasdad Nothing
>12da
>122
>dasd212
Hi Savas,
Since the datatype is CHAR (not VARCHAR), all values are padded with
spaces. And spaces are not numeric.
Try:
SELECT sal AS is_numeric
FROM savo
WHERE RTRIM(sal) NOT LIKE '%[^0-9]%' ;
SELECT sal AS is_alpha
FROM savo
WHERE RTRIM(sal) LIKE '%[^0-9]%' ;
Hugo Kornelis, SQL Server MVP|||isnumeric will not work reliably in a situation like this as "numbers" with
an e or a d (I think a few others also) will match as numeric according to
this function.
"Carl Johansen" <carl@._NOSPAM_carljohansen.co.uk> wrote in message
news:drgcjq$r6f$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> SELECT sal, isnumeric(sal)
> FROM savo
> "Savas Ates" <in da club> wrote in message
> news:eyFEMjBJGHA.3752@.TK2MSFTNGP11.phx.gbl...
>