This function is available in the Free version, and works in every version of SQL Server starting with SQL Server 2005: SELECT SQL#.String_TryParseToDecimal('7. And, there is a pre-done function that does this in the SQL# library (that I wrote) named String_TryParseToDecimal. I was hoping to find something more elegant than two REPLACE calls, but so far no such luck.Īlso, just to mention, while not a pure T-SQL solution, this can also be accomplished via SQLCLR. If the input string is empty, the LENGTH returns 0. The following illustrates the syntax of the LENGTH function. Some database systems use the LEN function that has the same effect as the LENGTH function.

The LENGTH function is available in every relational database systems.

The FORMAT function, but the input type must be a numeric or date/time/datetime value (that and it was introduced in SQL Server 2012, so not applicable to SQL Server 2008 R2 or older).Īnd nothing else seemed to work. The SQL LENGTH function returns the number of characters in a string. Looking at various format styles for CONVERT, but nothing applies here. Just remember that as they are the same what works/does not work with TONUMBER is the same with DECFLOATFORMAT. Therefore, for the rest of this post I will be using TONUMBER. Then convert the comma into a period / decimal and you are done: SELECT CONVERT(NUMERIC(10, 2),įor the sake of completeness, I should mention that I also tried: In my opinion the TONUMBER is more descriptive of what this built in function does, converting a character string to a number. You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.)

( If you are using SQL Server 2012 or newer, please see answer for a cleaner approach.

Sql text to num