SQL Server Performance

Why the result is not numeric?

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

  1. tuolang New 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. chiragkhabaria New 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. tuolang New Member

    Your function is good and effective.But how to explain my case? I want to know the reason.
  4. Roji. P. Thomas New Member

  5. kumar_boda New 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. chiragkhabaria New 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. chiragkhabaria New 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/
  8. Adriaan New Member

    A whole other point is of course that you have to ask yourself why you need to check if a string represents a numeric value.

    Is this to do with importing data into SQL Server? Or is this a call from a client application?
  9. Madhivanan Moderator

Share This Page