Hi,
How to match a string containing only numbers? The below method
doesn't works.
Please help.

declare @s varchar(30)
select @s="123"

if(@s like '[0-9]')
print "This string contains only numbers"
else
print "This string contains special characters"



Thanks,

On Jan 6, 9:54*pm, Subind <subind... (AT) gmail (DOT) com> wrote:
Hi

I think it is easier to check for presence of unwanted characters in
the string, for example:

declare @s varchar(30)
select @s="123"
if(@s not like '%[A-z]%')
print "This string contains only numbers"
else
print "This string contains special characters"
go

You may want to add additional unwanted characters to the list. In
addition, you may just try to convert the string to int with convert
function, but I found it difficult to handle the possible error
message properly in T-SQL.

Hope it helps
Leonid Gvirtz

"Subind" <subind123 (AT) gmail (DOT) com> wrote


For your particular example ("123") it could be

if (@s) like '[0-9][0-9][0-9]')

If you are on 15.0.1 or later use

if (isnumeric (@s) = 1)

At the moment I can't think of a more general solution (since you use a
varchar (30) and I assume that the arguments can be anything from NULL up to
30 characters "long").

"Carl Kayser" <kayser_c (AT) bls (DOT) gov> wrote

A pre-15.0.1 brute force solution:

declare @i smallint,
@special tinyint

set @i = 1,
@special = 0

while (@i < = 255)
begin
if (@i < 48 or @i > 57)
begin
if (char_index (char (@i), @s) > 0)
begin
set @i = 255,
@special = 1
end
end
set @i = @i + 1
end

if (@special = 1)
begin
print "This string contains special characters"
end
else
begin
print "This string contains only numbers"
end

On Jan 6, 12:54*pm, Subind <subind... (AT) gmail (DOT) com> wrote:

Do you mean "numbers" or "numeric digits"?

i.e., in terms of your problem, are any of the following strings
"numbers"?

1.0
-1
2.3e58
-2.3e-65
INF
NAN
-0.0
one thousand, two hundred and fifty-four
cinco
ichi
0xff

On Jan 8, 1:30 pm, Bret_Halford <b... (AT) sybase (DOT) com> wrote:
Regarding "cinco" -- That's not a number.

Eso es un número.

\:-\