Check data is numeric or text inside store procedure RSS

• • Reply
  

  gslakmal

  Member

  118 Points

  425 Posts

  Check data is numeric or text inside store procedure

  May 22, 2009 05:05 PM|LINK

   

  Hi,

  I have store proc. and i have table with vendorno filed. This field can contain both numeric and text data(eg. 123, asah23)

  I want check vendorno filed data is numeric or text, How can i check it inside store proc

  
  

  lakmal

  (MCPD MCTS) G S Lakmal
• • Reply
  

  Naom

  All-Star

  36004 Points

  7901 Posts

  Re: Check data is numeric or text inside store procedure

  May 22, 2009 05:09 PM|LINK

  Look at ISNUMERIC() function in BOL. 

  See also http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-aware 

  Beware of bugs in the above code; I have only proved it correct, not tried it.
  (Donald Knuth)
  
  Visit my blog
  
  Microsoft Community Contributor 2011-12
  • Edited by Naom on May 22, 2009 05:11 PM
  • Marked as answer by Jian Kang - MSFT on May 29, 2009 03:01 AM
• • Reply
  

  karan@dotnet

  All-Star

  26228 Points

  4596 Posts

  Re: Check data is numeric or text inside store procedure

  May 22, 2009 06:37 PM|LINK

   ISNUMERIC(vendorno)

  will reuturn 1 if numeric m 0 othewise.

  http://msdn.microsoft.com/en-us/library/ms186272.aspx
  

  Thanks,
  Karan
  
  ~ Blog ~
  
  Remember To Mark The Post(s) That Helped You As The ANSWER
• • Reply
  

  mo meng

  Contributor

  6700 Points

  1351 Posts

  Re: Check data is numeric or text inside store procedure

  May 23, 2009 07:05 AM|LINK

  but if use ISNUMERIC like this

  Select ISNUMERIC('1e23')
  Select ISNUMERIC('1d3')

  it will return 1 as well

• • Reply
  

  ASP.NET Dev

  Contributor

  4548 Points

  702 Posts

  Re: Check data is numeric or text inside store procedure

  May 23, 2009 08:37 AM|LINK

  This may help http://www.tek-tips.com/faqs.cfm?fid=6423

  http://stackexchange.com/users/71486/sandy
  • Marked as answer by Jian Kang - MSFT on May 29, 2009 02:59 AM