{"id":6987,"date":"2014-01-29T18:41:43","date_gmt":"2014-01-29T18:41:43","guid":{"rendered":"http:\/\/craftydba.com\/?p=6987"},"modified":"2016-04-20T01:42:33","modified_gmt":"2016-04-20T01:42:33","slug":"is-my-string-a-number","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6987","title":{"rendered":"Is my string a number?"},"content":{"rendered":"

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW<\/a>.<\/p>\n

Many times, you are just helping and\/or teaching younger SQL Server developers and\/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer.<\/p>\n

One classic questions asked by new database developers is whether or not the user input is a number?<\/p>\n

I thought it was an excellent opportunity to review what tools are available given a particular version of the database engine.<\/p>\n

If I was new to SQL Server, my first thought would be to use the ISNUMERIC()<\/a> function to determine if the text is really a number. This function has been part of the core engine since version 2005. <\/p>\n

The TSQL snippet below calls this function with valid and invalid data.<\/p>\n

\r\n\/*\r\n    Sample calls to isnumeric()\r\n*\/\r\n\r\n-- Test our old function\r\nselect 1 as test_no, isnumeric('1.0') as is_numeric\r\n  union all\r\nselect 2 as test_no, isnumeric('.5') as is_numeric\r\n  union all\r\nselect 3 as test_no, isnumeric('1.') as is_numeric\r\n  union all\r\nselect 4 as test_no, isnumeric(NULL) as is_numeric\r\n  union all\r\nselect 5 as test_no, isnumeric('.') as is_numeric\r\n  union all\r\nselect 6 as test_no, isnumeric('2.1.0') as is_numeric\r\ngo\r\n<\/pre>\n
It is interesting to note that a period is considered a number.  Please see screen shot below.<\/p>\n
\"old-function-call-is-numeric\"<\/a><\/p>\n
In version 2012, a bunch of new routines allow you to TRY a conversion.  Check out TRY_CAST()<\/a>, TRY_CONVERT()<\/a> and TRY_PARSE()<\/a> from books on-line.  Instead of returning a error during a failed conversion, they return a NULL value.  Let’s see how these routines convert a string that could be considered a decimal into a integer.<\/p>\n
\r\n\/*\r\n    Sample calls to try functions\r\n*\/\r\n\r\n-- returns null\r\ndeclare @a varchar(4) = '1.0';\r\nselect try_cast(@a as int);\r\n\r\n-- returns null\r\ndeclare @b varchar(4) = '1.0';\r\nselect try_convert(int, @b);\r\n\r\n-- returns 1\r\ndeclare @c varchar(4) = '1.0';\r\nselect try_parse(@c as int);\r\n<\/pre>\n
Two of the three functions return NULL which is what we do not want.  All three functions fail to ignore a period in the input.  On the other hand, all three functions work if the target data type is float.  We could always convert to float; then round or truncate to an integer?  <\/p>\n
However, I want a better solution.  Let’s write our own function!  <\/p>\n
Here are the business rules.  <\/p>\n
    \n
  1. detect non-numeric data, return 0<\/li>\n
  2. detect multiple periods, return 0<\/li>\n
  3. whole number data, return 1<\/li>\n
  4. decimal data, return -1<\/li>\n<\/ol>\n
    Let’s think about solving each business case separately.<\/p>\n
    To detect non-numeric data, we can use the LIKE function with a regular expression.  The TSQL snippet below solves our first problem.<\/p>\n
    \r\n\/*\r\n    Testing for non-numeric data\r\n*\/\r\n\r\ndeclare @d varchar(8) = '1a.0b';\r\nselect \r\n  case \r\n    when @d like '%[^0-9.]%' then 'non-numeric' \r\n    else 'numeric' \r\n  end as test;\r\n<\/pre>\n
    To detect multiple periods, we have to traverse the string looking for them.  There has to be a better way?  <\/p>\n
    Jeff Moden<\/a> made the concept of a tally table popular.  Here is my own version that is used to split the string at the single character level.  The results are a table with index position and character value.  <\/p>\n
    While this is not the fastest function, it is good enough for small strings and very tiny in code length.  See article by Aaron Bertrand<\/a> on a comparison of string splitting functions for performance.<\/p>\n
    \r\n\/*\r\n    usp_split_string() function\r\n*\/\r\n\r\n-- Use msdb\r\nuse msdb;\r\ngo\r\n\r\n-- Drop existing function\r\nif object_id('usp_split_string') > 0\r\ndrop function dbo.usp_split_string\r\ngo\r\n\r\n-- Create new function\r\ncreate function dbo.usp_split_string(@value varchar(4000))\r\nreturns table\r\nas\r\nreturn\r\n(\r\n    with tally as\r\n    (\r\n       select \r\n           row_number() over (order by (select 1)) as n\r\n       from \r\n           sys.all_columns x cross apply sys.all_columns y\r\n    )\r\n    select top 100\r\n        n as idx, \r\n        substring(@value, n, 1) as val\r\n    from tally\r\n    where n <= len(@value)\r\n    order by n\r\n);\r\ngo\r\n\r\n-- Sample call\r\nselect * from dbo.usp_split_string('This is my simple string');\r\ngo\r\n<\/pre>\n
    Here are the results of a sample call.<\/p>\n
    \"string-splitting-example\"<\/a><\/p>\n
    The next step is to create a function using the split string function that counts the occurrence of any given character in a string.<\/p>\n
    \r\n\/*\r\n    usp_count_chars() function\r\n*\/\r\n\r\n-- Use msdb\r\nuse msdb;\r\ngo\r\n\r\n-- Drop existing function\r\nif object_id('usp_count_chars') > 0\r\ndrop function dbo.usp_count_chars\r\ngo\r\n\r\n-- Create new function\r\ncreate function dbo.usp_count_chars(@value varchar(4000), @char varchar(1))\r\nreturns table\r\nas\r\nreturn\r\n(\r\n    select count(val) as cnt\r\n    from dbo.usp_split_string (@value)\r\n    where val = @char\r\n);\r\ngo\r\n\r\n-- Sample call\r\nselect * from dbo.usp_count_chars('This is my simple string', 'i');\r\ngo\r\n<\/pre>\n
    The sample call correctly detects 4 i’s in the phrase.<\/p>\n
    \"count-the-number-of-characters\"<\/a><\/p>\n
    You might have not noticed, but I am creating In-line table valued functions.  They are faster than regular functions.  See Wayne Sheffield\u2019s blog article<\/a> that attests to this fact.  <\/p>\n
    I am also installing these functions in the [msdb] database so that you can re-use them in your programming adventures.<\/p>\n
    Last but not least, we need to pull it all together to write are improved function.  We will use a case statement so that we can handle issues like null values, multiple periods or just a period.  Please note, I choose a scalar function so that the new function can be used like the old one, ISNUMERIC().<\/p>\n
    \r\n\/*\r\n    usp_is_numeric() function\r\n*\/\r\n\r\n-- Drop existing function\r\nif object_id('usp_is_numeric') > 0\r\ndrop function dbo.usp_is_numeric\r\ngo\r\n\r\n-- Create new function\r\ncreate function dbo.usp_is_numeric(@value varchar(4000))\r\nreturns int\r\nas\r\nbegin\r\n  return\r\n  (\r\n    select \r\n        case \r\n\t    when @value is null then 0\r\n\t    when rtrim(ltrim(@value)) = '.' then 0 \r\n\t    when @value like '%[^0-9.]%' then 0 \r\n\t    when period.cnt > 1 then 0\r\n\t    when period.cnt = 1 then -1\r\n\t    else 1 \r\n        end \r\n    from \r\n        dbo.usp_count_chars(@value, '.') as period\r\n  )\r\nend;\r\ngo\r\n\r\n-- Test our new function\r\nselect 1 as test_no, msdb.dbo.usp_is_numeric('1') as is_numeric\r\n  union all\r\nselect 2 as test_no, msdb.dbo.usp_is_numeric('.5') as is_numeric\r\n  union all\r\nselect 3 as test_no, msdb.dbo.usp_is_numeric('1.') as is_numeric\r\n  union all\r\nselect 4 as test_no, msdb.dbo.usp_is_numeric(NULL) as is_numeric\r\n  union all\r\nselect 5 as test_no, msdb.dbo.usp_is_numeric('.') as is_numeric\r\n  union all\r\nselect 6 as test_no, msdb.dbo.usp_is_numeric('2.1.0') as is_numeric\r\ngo\r\n<\/pre>\n
    The output shows that the new function ignores bad data, detects numeric values and supports decimal values.  Given this output, you can correctly make decisions on how to convert the string to a variable.<\/p>\n
    \"new-function-call-usp-is-numeric\"<\/a><\/p>\n
    In summary, this was a great opportunity to explore tally tables, split string functions, inline table value functions and regular scalar functions.  Three new functions have been created that you can use to solve all sorts of programming tasks.  <\/p>\n
    I hope you enjoyed this article as much as I did writing it.<\/p>\n","protected":false},"excerpt":{"rendered":"
    I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and\/or teaching younger SQL Server developers and\/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One classic questions asked by new database developers is whether or not the user input is a number? I thought it was an excellent opportunity to review what tools are available given a…<\/p>\n","protected":false},"author":1,"featured_media":6988,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,1032,15,1034,1005,1033,899,898,900,29,1036,1037,1035],"class_list":["post-6987","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-db-dev","tag-database-developer","tag-isnumeric","tag-john-f-miner-iii","tag-split-string","tag-switchoffset","tag-tally-table","tag-try_cast","tag-try_convert","tag-try_parse","tag-tsql","tag-usp_count_chars","tag-usp_is_numeric","tag-usp_split_string"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6987","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6987"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6987\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6988"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6987"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6987"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}