Tag: string functions

How to Apply Conditional Formatting to a Number in SQL Server using FORMAT()

Posted on by Ian

Perhaps one of the lesser-known features of the FORMAT() function in SQL Server is one that enables you to apply conditional formatting to a number.

It’s more of a .NET feature than it is a SQL Server (or T-SQL) feature, but SQL Server/T-SQL supports it all the same, allowing you to take full advantage of the ability to apply conditional formatting to numbers.

It all comes down to the format string you pass to the FORMAT() function.

You can pass a format string that specifies how the number should be formatted, depending on whether it’s positive, negative, or zero.

Continue reading →

What is a Format String in SQL Server?

Posted on by Ian

In SQL Server, the FORMAT() function enables you to format date/time and number values as a formatted string by passing in a “format string” as the second argument (the first argument is the value that’s being formatted).

Here’s an example of this function in action:

FORMAT(@date, 'dd/MM/yyyy');

In this case the format string is dd/MM/yyyy.

This particular format string specifies that the @date value should be formatted with a two-digit day, two-digit month, and a four-digit year, in that order, and with forward slashes as the separators.

This would result in something like this:

21/05/2019

Continue reading →

Custom Date/Time Format Strings Supported by FORMAT() in SQL Server

Posted on by Ian

This article provides a reference for the custom date and time format specifiers that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples.

You can combine any of these format specifiers to produce a customized format string. See below for a code example and an explanation on how custom format strings work.

Continue reading →

Standard Date/Time Format Strings Supported by FORMAT() in SQL Server

Posted on by Ian

This article provides a list of the standard date and time format strings that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples using different cultures and data types.

These are the standard date and time format specifiers that are supported by the .NET Framework, and therefore, SQL Server. Each standard date and time format string is an alias for a custom date and time format string.

Continue reading →

How to Specify the Invariant Culture when using FORMAT() in SQL Server

Posted on by Ian

In SQL Server, you can use the FORMAT() function to format date/time and number values as strings. In particular, the function provides “locale-aware” formatting, and the function accepts a “culture” argument, which allows you to specify a culture or language to use for the actual format. For example, you can pass en-us to ensure the results are formatted in US English format.

The culture argument is optional, so if you don’t provide it, the output will be determined by the language of the current session.

The FORMAT() function accepts any culture supported by the .NET Framework as an argument (you’re not limited to the languages explicitly supported by SQL Server).

One of the cultures supported by the .NET Framework is the invariant culture. The invariant culture is culture-insensitive. More specifically, this culture is associated with the English language but not with any country/region.

To specify that FORMAT() should output the results using the invariant culture, simply use "iv" for the culture argument (the third argument).

Continue reading →

Remember This When Formatting a TIME Data Type in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, when you use the T-SQL FORMAT() function to format a time data type, you need to remember to escape any colons or periods in your format string.

This is because the FORMAT() function relies upon CLR formatting rules, which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.

Continue reading →

How to Format Numbers as Currency in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, you can use the T-SQL FORMAT() function to format a number as a currency.

The FORMAT() function allows you to format numbers, dates, currencies, etc. It accepts three arguments; the number, the format, and an optional “culture” argument. This article specifically deals with using the format argument to specify a currency.

Continue reading →

SQL Server String Functions (Full List)

Posted on by Ian

Transact-SQL (T-SQL) includes a number of scalar functions that allow us to perform operation on strings when working with SQL Server. These functions accept an input string, and return either a string or a numeric value.

The following is a list of T-SQL string functions available in SQL Server. Click on each function or operator name to see an explanation of the function, its syntax, and examples.

Continue reading →

How the TRANSLATE() Function Works in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, the T-SQL TRANSLATE() function returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.

It’s similar to the REPLACE() function, but with some important differences.

The TRANSLATE() function requires 3 arguments; the input string, the characters to be replaced, and the characters to replace them.

Continue reading →