SDU Tools: Finding the current session decimal separator in SQL Server T-SQL

I often say that I love writing code that writes code. I use T-SQL to generate T-SQL scripts all the time. (I use Excel to do it too but that’s a story for another day). An issue I ran into a while back though, was how to output decimal numbers. I wanted to make sure I was using the correct decimal separator for the user who was running the script.

If you’re not aware, it’s common in European countries to write numbers like 234232,45 where in other countries we’d write 234232.45 instead. You can see that in the main image above where I’ve output a value with German culture.

The CurrentSessionDecimalSeparator function in our free SDU Tools for developers and DBAs, does this. It provides the separator that’s currently in use.

It takes no parameters.

And of course, it’s also important to get the thousands separator if one of those is needed. So we’ve provided CurrentSessionThousandsSeparator as well.

Find out more

You can see them in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE OR ALTER FUNCTION SDU_Tools.CurrentSessionDecimalSeparator()
RETURNS varchar(1)
AS
BEGIN

-- Function:      Returns the decimal separator for the current session
-- Parameters:    None
-- Action:        Works out what the decimal separator is for the current session
-- Return:        varchar(1)
-- Refer to this video: https://youtu.be/Jy_qVDOjUzI
--
-- Test examples: 
/*

SELECT CAST(FORMAT(123456.789, 'N', 'de-de') AS varchar(20));
SELECT SDU_Tools.CurrentSessionDecimalSeparator();

*/
    RETURN SUBSTRING(CAST(CAST(0 AS decimal(18,2)) AS varchar(20)), 2, 1);
END;
GO

CREATE OR ALTER FUNCTION SDU_Tools.CurrentSessionThousandsSeparator()
RETURNS varchar(1)
AS
BEGIN

-- Function:      Returns the thousands separator for the current session
-- Parameters:    None
-- Action:        Works out what the thousands separator is for the current session
-- Return:        varchar(1)
-- Refer to this video: https://youtu.be/Jy_qVDOjUzI
--
-- Test examples: 
/*

SELECT CAST(FORMAT(123456.789, 'N', 'de-de') AS varchar(20));
SELECT SDU_Tools.CurrentSessionThousandsSeparator();

*/
    RETURN SUBSTRING(CONVERT(varchar(20), CAST(1000 AS money), 1), 2, 1);
END;
GO

2020-06-03