SQL Server Data Types To JSON Data Types

Facebooktwitterredditpinterestlinkedinmail

In SQL Server 2016 they are added support for outputting your query results in JSON. When you serialize your data to JSON using the FOR JSON clause, a data type conversion happens. The data types are converted from SQL Server data types to JSON supported data types.

For more information on how to convert your result set to JSON: www.sqlserver.info/syntax/json-in-sql-server/

Here is a quick reference table showing the data type conversions that are happening:

[table width=”500″ colwidth=”250|250″ colalign=”left|left”]
SQL Data Type,JSON Data Type
VARCHAR/NVARCHAR,String
CHAR/NCHAR,String
INT,Number
BIGINT,Number
FLOAT,Number
DECIMAL,Number
NUMERIC,Number
BIT,Boolean
DATETIME,String
DATETIME2,String
DATE,String
TIME,String
DATETIMEOFFSET,String
VARBINARY,Base64-encoded string
BINARY,Base64-encoded string
IMAGE,Base64-encoded string
TIMESTAMP,Base64-encoded string
ROWVERSION,Base64-encoded string
UNIQUEIIDENTIFIER,String
MONEY,String
[/table]

 

SQL Server Zero Pad Left

Facebooktwitterredditpinterestlinkedinmail

In a recent project, I had the task of padding a number with zeros. SQL Server doesn’t have a built in Zero Pad Left function. SQL Server does have a Right function. You can use this to create a zero padded left string.

SELECT	RIGHT('0000000000' + @TextToZeroPad, 10) AS ZeroPaddedText

In the code above, I am take 10 zeros, appending a text value to it, then taking the right 10 characters. The end result is the same as padding the left with zeros.

 
SQL Server Zero Pad Left Function
You can take this a step further and create a scalar function out of it.

CREATE	FUNCTION PadLeft
		(@TextToPad 	 	VARCHAR(8000),
		 @CharacterToPad	VARCHAR(1),
		 @NumberToPad	 	INT)
RETURNS		VARCHAR(8000)
AS
BEGIN
	DECLARE	@OutputText VARCHAR(8000)
	
	-- Create a string with the maximum number of padded characters followed
	-- by the text that we want to pad
	SET		@OutputText = REPLICATE(@CharacterToPad, @NumberToPad) + @TextToPad
	
	-- Take the right X characters to simulate the padding
	RETURN	RIGHT(@OutputText, @NumberToPad)
END

SQL Server Truncate Date

Facebooktwitterredditpinterestlinkedinmail

A common function that people need to do when dealing with datetimes is extracting the date from the datetime.  Oracle has a built-in function to do this called TRUNC.  SQL Server does not have this (yet).  However… you can accomplish this very easily.  Here are a few ways to perform a truncate date function in SQL Server.

-- Assign date to variable
DECLARE	@DateToTruncate	DATETIME = '2014-08-01 14:12:34'

-- Get the date using casts
SELECT	CAST(CAST(@DateToTruncate AS DATE) AS DATETIME)

-- Get the date using convert
SELECT	CAST(CONVERT(VARCHAR(10), @DateToTruncate, 101) AS DATETIME)

 

SQL Server Trim

Facebooktwitterredditpinterestlinkedinmail

SQL Server trim function does not exist!  Although this seems a bit odd, SQL Server does somewhat have support for trimming data.  SQL Server has built in functions for Right Trim and for Left Trim.  Using these functions, you can accomplish a full trim.  I’ll first show you how to trim the different sides of the text and then show how to trim the full thing.

 
Trim Text Inline

The functions to trim text are RTrim() and LTrim().

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

-- Trims the spaces from the left side of the data
SELECT	LTRIM(@TextToTrim) AS LeftSpacesRemoved

-- Trims the spaces from the right side of the data
SELECT	RTRIM(@TextToTrim) AS RightSpacesRemoved

-- Trims the spaces from both sides of the data
SELECT	LTRIM(RTRIM(@TextToTrim)) AS FullTrim

 
If a full trim is something that you do often, you can create a SQL Server Trim function to make the process a little simpler.

CREATE	FUNCTION TRIM(@TextToTrim VARCHAR(8000))
RETURNS	VARCHAR(8000)
BEGIN

	RETURN	LTRIM(RTRIM(@TextToTrim))

END

You can use this new fancy SQL Server Trim function by calling it like this:

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

SELECT	dbo.TRIM(@TextToTrim) AS FullTrim

How To Format A Number With Commas

Facebooktwitterredditpinterestlinkedinmail

SQL Server 2012 makes it a lot easier to format a number with commas, but let’s face it… previous versions haven’t been so nice to us.  Here are the easiest ways that I know to format a number with commas.

SQL Server 2012+

SELECT	FORMAT(1234567890, '###,###,###') AS 'FormattedNumber'

SQL Server 2008 And Previous

SELECT	REPLACE(CONVERT(VARCHAR(50), (CAST(1234567890 AS money)), 1), '.00', '') AS FormattedNumber