Is my string a number?

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 particular version of the database engine.

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

The TSQL snippet below calls this function with valid and invalid data.

It is interesting to note that a period is considered a number. Please see screen shot below.

old-function-call-is-numeric

In version 2012, a bunch of new routines allow you to TRY a conversion. Check out TRY_CAST(), TRY_CONVERT() and TRY_PARSE() 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.

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?

However, I want a better solution. Let’s write our own function!

Here are the business rules.

  1. detect non-numeric data, return 0
  2. detect multiple periods, return 0
  3. whole number data, return 1
  4. decimal data, return -1

Let’s think about solving each business case separately.

To detect non-numeric data, we can use the LIKE function with a regular expression. The TSQL snippet below solves our first problem.

To detect multiple periods, we have to traverse the string looking for them. There has to be a better way?

Jeff Moden 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.

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 on a comparison of string splitting functions for performance.

Here are the results of a sample call.

string-splitting-example

The next step is to create a function using the split string function that counts the occurrence of any given character in a string.

The sample call correctly detects 4 i’s in the phrase.

count-the-number-of-characters

You might have not noticed, but I am creating In-line table valued functions. They are faster than regular functions. See Wayne Sheffield’s blog article that attests to this fact.

I am also installing these functions in the [msdb] database so that you can re-use them in your programming adventures.

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().

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.

new-function-call-usp-is-numeric

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.

I hope you enjoyed this article as much as I did writing it.

Related posts

2 Thoughts to “Is my string a number?”

  1. Mario

    You could rephrase business rule 2, into “if #periods >= 2 return 0”
    Which translates into the following:
    sign(charindex(‘.’,yoursearchstring)) + sign(coalesce(charindex(‘.’,substr(yoursearchstring,charindex(‘.’,yoursearchstring) + 1, len(yoursearchstring))).0))

    sign(charindex(‘.’,yoursearchstring)) –> first period
    sign(coalesce(charindex(‘.’,substr(yoursearchstring,charindex(‘.’,yoursearchstring) + 1, len(yoursearchstring))).0)) –> second period

    (I originally was looking for info on PowerBI, but thought your posts quite good/interesting)

    1. Mario

      There is a typo:
      sign(coalesce(charindex(‘.’,substr(yoursearchstring,charindex(‘.’,yoursearchstring) + 1, len(yoursearchstring))),0)).

Leave a Comment