• 13. 12. 2019
  • Ing. Jan Zedníček - Data Engineer & Controlling
  • 0

T-SQL is not a language designed primarily to do something big with text strings or manipulate with them. That is the reason why we will not find many text-related built-in functions. But we will definitely find use for those that are there. Let’s take a look at SQL String Functions.

Text Concatenation (Combining) – CONCAT Function

Concatenation is one of the most common operations with strings. We have 2 options in TSQL:

  • using (+) operator
  • applying CONCAT() function

Syntax: CONCAT(text 1, text 2, text 3)

I really recommend to use CONCAT since making strings via (+) operator can be treacherous. If there is a string that is NULL, the result of a combination via (+) operator will always be NULL. This situation can be solved in many different ways. Using the CONCAT function takes no work at all.

Look at an example:

DECLARE @Par1 AS VARCHAR(15) = 'Data Warehouse '
DECLARE @Par2 AS VARCHAR(10) = 'is great'

CONCAT(@Par1,@Par2,@Par3) AS [Result - CONCAT],
@Par1 + @Par2 + @Par3 AS [Result (+) operator]

concat vs plus operator - combining text


a) SUBSTRING is an ideal candidate to use in cases when we have a text string containing patern – rule. Using SUBSTRING we can easily extract the part of the text that interests us.

Syntax: SUBSTRING(text, starting_position, length)



String functions - SUBSTRING example

b) CHARINDEX – We often do not know a position of the first character or we only know that regarding the searched text there is a character from which we want to extract certain number of characters while the position of given character may vary. In this case it is best to combine SUBSTRING function with CHARINDEX which will find us the starting position of the character as a SUBSTRING argument. Similar process will allow us to play with substring argument on number of extracted symbols using this or other SQL string functions.

Syntax: CHARINDEX(Searched string, String to be searched)



CHARINDEX('Hi',@Par1) AS [Starting Position]

String functions - CHARINDEX with SUBSTRING example

c) LEFT, RIGHT – When we need to extract part of the text starting with first symbol from right or left we can use these functions. Command LEFT(‘xyz’,1) returns first symbol from left, that is x.

Syntax: LEFT(Searched text, number of characters to be extracted)

Finding Out the Length of String – LEN, DATALENGHT Functions

It is sometimes useful to know the length of text string. We can use 2 functions for this purpose – LEN and DATALENGHT

a) LEN function – returns length of entry chain in aspect of symbol count. For example command LEN(xyz) would return value 3. If there are spaces on the end of the text, function will erase them.

Syntax: LEN(Text)

b) DATALENGTH function – returns length from aspect of bytes. This means that if the entry string is Unicode, the function will count 2 bytes for each symbol. For example command DATALENGTH(N’xyz’) will return 6. Function DATALENGTH does not erase spaces as LEN does.

Syntax: DATALENGHT(Text)

Change of Text String – REPLACE, REPLICATE Functions

a) REPLACE FunctionIf we want to replace previously defined part of text, we use REPLACE. This function replaces previously selected part of the text for a different one. Command REPLACE(‘a.b.c’, ‘.’, ‘-‘) Returns value a-b-c

Syntax: REPLACE(text in which we replace, replaced string, string)

b) REPLICATE Function – We use REPLICATE to repeat particular part of the text by needed number of repetitions. Result of REPLICATE(‘Hi ‘, 3) command will be text Hi Hi Hi

Syntax: REPLICATE(Text, number of repetitions)

Text Strings Formatting Using SQL Functions – UPPER, LOWER, LTRIM, RTRIM

These functions do not need any in-depth explaining. They explain themselves:

  1. UPPER – returns text as upper case letters
  2. LOWER – returns text as lower case letters
  3. LTRIM – deletes spaces on the left
  4. RTRIM – deletes spaces on the right


Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *