November 3, 2008 at 8:28 am
Hi is there a function which determines if a varchar is a number?
E.g.
Select * from table where filed <> to a number
November 3, 2008 at 8:36 am
you can try the isnumeric function:
WHERE ISNUMERIC(SOMEVARCHAR) = 1
but as others will tell you, it's not 100% reliable, because of the way it is structured; for exampel
select isnumeric('1E2')
select isnumeric('1d2')
all return 1(ttue, because E means exponent, or d means decimal...so it is possible a bad value could get thru;
I've got these two functions saved in my snippets, isReallyNumeric and isReallyInteger, which are a refinement of the question in a previous thread.
CREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos TINYINT
SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))
RETURN CASE
WHEN PATINDEX('%[0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO
CREATE FUNCTION dbo.isReallyInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
RETURN CASE
WHEN PATINDEX('%[0-9-]%', @num) = 0
AND CHARINDEX('-', @num) 1
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO
Lowell
November 3, 2008 at 8:39 am
Many thanks extremely helpful
November 3, 2008 at 12:29 pm
Edward (11/3/2008)
Hi is there a function which determines if a varchar is a number?
What kind of number?
Integer, decimal, float?
Is "$100.00-" a number for your application?
For some it is.
_____________
Code for TallyGenerator
November 4, 2008 at 5:59 am
Lowell (11/3/2008)
you can try the isnumeric function:WHERE ISNUMERIC(SOMEVARCHAR) = 1
but as others will tell you, it's not 100% reliable, because of the way it is structured; for exampel
select isnumeric('1E2')
select isnumeric('1d2')
all return 1(ttue, because E means exponent, or d means decimal...so it is possible a bad value could get thru;
I've got these two functions saved in my snippets, isReallyNumeric and isReallyInteger, which are a refinement of the question in a previous thread.
[font="Courier New"]CREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos TINYINT
SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))
RETURN CASE
WHEN PATINDEX('%[0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO
CREATE FUNCTION dbo.isReallyInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
RETURN CASE
WHEN PATINDEX('%[0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO[/font]
Lowell, what's the previous post link? Just curious, but wouldn't it be just as easy to multiply by one and see if you get a result or an error?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 4, 2008 at 6:15 am
the previous link was a 20+ page forum discussion here on SSC;When I used the Google tool, it did not find the thread; I grabbed what items I thought were useful from it, and these were two of them.
Lowell
November 4, 2008 at 6:26 am
thanks, I couldn't find it either.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 16, 2009 at 6:06 am
Thanks for this Script. It really helped me alot. Since i`m developing for an german enterprise i rewrote the script to use "," instead of ".". I also asume that the last char has to be a number:
USE [IFCC]
GO
/****** Objekt: UserDefinedFunction [dbo].[isReallyNumeric] Skriptdatum: 04/16/2009 13:51:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[isReallyNumeric]
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos TINYINT
SET @pos = 1 + LEN(@num) - CHARINDEX(',', REVERSE(@num))
RETURN CASE
WHEN PATINDEX('%[^0-9,-]%', @num) = 0
AND PATINDEX('[0-9]%',REVERSE(@num)) != 0 --the last digit has to be a number
AND @num NOT IN (',', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1) --there is no ,
OR @pos = CHARINDEX(',', @num)) --there is only 1 ,
)
THEN
1
ELSE
0
END
END
April 16, 2009 at 6:25 am
glad you found it Dom;
this site has a Ton of really useful scripts. enjoy.
i cleaned up my original post, since the update to the CODE window made HTML font info look bad .
Lowell
April 17, 2009 at 7:09 am
How about this code?
Declare @STR varchar(30)
select @STR = 'karTHI&%@#123'
select substring(@Str,N,1),ascii(substring(@Str,N,1)) as AsciiValue,
case when (ascii(substring(@Str,N,1)) >= 65 and ascii(substring(@Str,N,1)) = 97 and ascii(substring(@Str,N,1)) <= 122)
or
(ascii(substring(@Str,N,1)) in (35,36,37,38,64))
then 1 else 0 end as Status
from Tally
where N <= len(@Str)
karthik
April 24, 2010 at 2:07 pm
why 9 returns false for IsRealyNumeric?
April 24, 2010 at 2:42 pm
Edward-445599 (11/3/2008)
Hi is there a function which determines if a varchar is a number?E.g.
Select * from table where filed <> to a number
Let's ask a question here... is the intent to determine if the "field" contains only digits from 0 to 9???
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply