# Why the result is not numeric?

Discussion in 'General Developer Questions' started by tuolang, Oct 7, 2006.

1. ### tuolangNew Member

Why the result is not numeric?Even if 'd' is repalced 'e'.But if it is else letter,the result is right.

declare @b varchar(10)
set @b='1b2d32'
while isnumeric(@b)=0
set @b=replace(@b,substring(@b,PATINDEX('%[a-z]%',@b),1),'')
select @b,isnumeric(@b)
2. ### chiragkhabariaNew Member

Have a look at the following function which will remove all the text type from the function and make it a numeric string <br /><br />Hoep this helps you <br /><pre id="code"><font face="courier" size="2" id="code"><br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MakeNumeric]') and xtype in (N'FN', N'IF', N'TF'))<br />drop function [dbo].[MakeNumeric]<br />GO<br /><br /><br />CREATE Function MakeNumeric <br />(<br />@AlphaNum varchar(1000)<br />)<br />Returns Int <br />As<br />Begin <br />Declare @VarNum Varchar(100),<br />@Numint<br /><br />Select @VarNum = '', @Num = null <br /><br />SELECT@VarNum = @VarNum + parttext From <br />(<br />Select Top 100 percent SUBSTRING(REPLACE(@AlphaNum, ' ', ''), n.x + 1, 1) parttext<br />FROM(<br />SELECTb9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i x<br />FROM(SELECT 0 i UNION ALL SELECT 1) b0<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT <img src='/community/emoticons/emotion-11.gif' alt='8)' /> b3<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> b7<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 512) b9<br />WHEREb9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i &lt; 800<br />) n<br />WHEREn.x &lt; LEN(REPLACE(@AlphaNum, ' ', '')) <br />ORDER BYn.x<br />) As F <br />Where IsNumeric(parttext)=1<br /><br />if Isnumeric(@VarNum) = 1<br /> Select @Num = Convert(int,@VarNum) <br /><br />return @Num<br /><br />End <br /><br /><br />GO<br />declare @b varchar(10)<br />set @b='1b2d32'<br />Select @b = Dbo.MakeNumeric(@b)<br /><br />select @b,isnumeric(@b)<br /><br /></font id="code"></pre id="code"><br /><br />Chirag
3. ### tuolangNew Member

Your function is good and effective.But how to explain my case? I want to know the reason.

5. ### kumar_bodaNew Member

We can do the same without using any UDF's

Have a look at this

DECLARE @b VARCHAR(100)
DECLARE @S VARCHAR(100)

SET @b='1b2d32as46d'

SELECT @S = COALESCE (@S+'', '' ) + CASE WHEN ISNUMERIC(SUBSTRING(@b, number, 1)) = 0 THEN ''
ELSE SUBSTRING(@b, number, 1) END
FROM Master..spt_Values
WHERE SUBSTRING(@b, number, 1) <> ''
AND TYPE = 'P'
SELECT @S

but the problem is number < 256

Thank's
Anil
6. ### chiragkhabariaNew Member

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by tuolang</i><br /><br />Your function is good and effective.But how to explain my case? I want to know the reason.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />If you look at the link which Roji posted, then you will understand that why its not a good technique to use IsNumeric function <br /><br />Check out this function for checking if the string is numeric or not <br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />/* Is function returns 0 then its not a numeric function if 1 then its a number function */<br />Create Function IsNumber<br />(<br />@pNumericString varchar(1000)<br />)<br />Returns bit <br />As <br />Begin <br />Declare @Count Int<br />SELECT@Count = Count(parttext) From <br />(<br />Select Top 100 percent SUBSTRING(REPLACE(@pNumericString, ' ', ''), n.x + 1, 1) parttext<br />FROM(<br />SELECTb9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i x<br />FROM(SELECT 0 i UNION ALL SELECT 1) b0<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT <img src='/community/emoticons/emotion-11.gif' alt='8)' /> b3<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> b7<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8<br />CROSS JOIN(SELECT 0 i UNION ALL SELECT 512) b9<br />WHEREb9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i &lt; 800<br />) n<br />WHEREn.x &lt; LEN(REPLACE(@pNumericString, ' ', '')) <br />ORDER BYn.x<br />) As F <br />Where parttext like '%[^0-9]%'<br /><br />if @Count &lt;1 -- Yes there is some string <br />return 0<br /><br />return 1<br />End <br /><br />GO<br />declare @b varchar(10)<br />set @b='1b2d32'<br />while dbo.IsNumber(@b)=1<br />set @b=replace(@b,substring(@b,PATINDEX('%[a-z]%',@b),1),'')<br /><br />select @b,isnumeric(@b)<br /></font id="code"></pre id="code"><br /><br />Chirag<br /><br /<a target="_blank" href=http://chirikworld.blogspot.com/>http://chirikworld.blogspot.com/</a>
7. ### chiragkhabariaNew Member

quote:Originally posted by kumar_boda

We can do the same without using any UDF's

Have a look at this

DECLARE @b VARCHAR(100)
DECLARE @S VARCHAR(100)

SET @b='1b2d32as46d'

SELECT @S = COALESCE (@S+'', '' ) + CASE WHEN ISNUMERIC(SUBSTRING(@b, number, 1)) = 0 THEN ''
ELSE SUBSTRING(@b, number, 1) END
FROM Master..spt_Values
WHERE SUBSTRING(@b, number, 1) <> ''
AND TYPE = 'P'
SELECT @S

but the problem is number < 256

Thank's
Anil

Yes Anil, I agree to it that you dont require the function in this case, but when you want to scan a records based on the select query then, according to me the function is the best deal.

Chirag

http://chirikworld.blogspot.com/