Part 1: String Utilities
There are certain functions that seem to come up in every programming language. Although SQL differs from your typical procedural programming language like C# or Java in many ways, it too comes equipped with an impressive assortment of built-in functions. These may be applied to Char, Varchar, and Text data types. Each database vendor does not implement functions in exactly the same way, so it pays to familiarize yourself with functions that are specific to the database you work with. In this series, we'll be taking a look at a few important SQL functions, as implemented by SQL Server. Today's blog will tackle string functions, while subsequent installments will explore numerical, date functions, and more!
Len
One of the most useful string functions is one that returns its length in characters (including spaces and punctuation). In Microsoft products, there is a long tradition of calling this function "Len". Here's the function signature:
LEN(inputString)
As an example, we'll execute a real query against the Sakila Sample Database using Navicat for SQL Server as our database client. The query selects the top 10 longest titles from the film table in descending order:
Trim
Looking to trim some fat off of a string? Then the trim function is for you! It eliminates excess spaces and tabs from the beginning and end of a string that we pass in as its argument. Here's the signature for trim:
TRIM(inputString)
We can use trim to find out if any of our film titles contain any leading or trailing spaces by comparing the length of the trimmed title to what's there currently:
Concat
In programming, the combining of strings is known as concatenation. Hence, the concat function combines two or more strings that we pass in as its arguments. Here's its signature:
CONCAT(string1, string2, ...., string_n)
The concat function is really useful to format multiple columns together in a way that works for you and your users. The following query combines the ID, title, and release year for each film and separates them using commas:
Upper & Lower
These two counterpart functions take a string argument and return the same string but with all its characters cast to uppercase and lowercase, respectively.
UPPER(inputString)
To show the effects of the Upper & Lower functions, we can show film titles in their original case and altered through each function:
Working with Functions in Navicat
One of the features of Navicat's SQL Editor is auto-completion. As soon as you begin to type a word, a list of suggestions comes up that includes all database objects, including schema, table/view, column, procedure, and, of course, function names:
Once a function (or stored procedure) is selected, input parameters are highlighted for entry. If there are more than one, each parameter is tabbable for quick access:
Conclusion
In this first instalment of this series on Important SQL Server Functions, we looked at several useful string utility functions, including Len, Trim, Concat, Upper, and Lower. Next time, we'll be moving on to numeric functions.
Interested in Navicat for SQL Server? You can try it for 14 days completely free of charge for evaluation purposes!
