Functions for positions

1. Functions for positions

You made it to the second half of this course, good job! You now know how to work with the most common data types in SQL Server, how to perform conversions from a data type to another and have more in-depth knowledge about using date functions. This chapter focuses on learning what you can do with the functions that manipulate strings in SQL Server. Often, in practice, we have to validate that the length of a field does not exceed a certain maximum value, check that an email address comes from a specific provider or find how many fields contain a certain expression. You will learn how to do these things with functions for strings positions.

2. Position functions

The functions returning the position of an expression within a string are: LEN(), CHARINDEX() and PATINDEX(). Let’s look at them one at a time.

3. LEN()

LEN(), short for length, returns the number of characters from the provided string, excluding the blanks at the end. It has only one parameter: the string whose length you want to calculate.

4. LEN() example - constant parameter

This is an example of using the length function with a constant parameter. Obviously, no matter how many times you will execute this statement, it will always return the dimension of the parameter string.

5. LEN() example - table column parameter

Here is another example, where I apply the LEN() function to the "bean_origin" column. The result represents the length of each string stored in this column.

6. CHARINDEX()

The CHARINDEX() function looks for a character expression in a given string and returns the starting position of the first occurrence it finds. It has 2 mandatory parameters and 1 optional parameter: the expression we are looking for,the string in which we do the search and a value expressing the starting position of the search. If this is not specified, the search will start at the beginning.

7. CHARINDEX() example

Let's look at some examples. The first call returns the position of the first occurrence of the word "chocolate" in the expression "White chocolate is not real chocolate", which is 7. In the second example, we specify the location parameter, meaning that the search of the word "chocolate" will start after the first 10 characters. The result is the second appearance of the word, which starts at position 29. In the 3rd call, we are looking for an expression that does not exist in the string. In this situation, 0 is returned.

8. PATINDEX()

PATINDEX() is similar to CHARINDEX() but more powerful. It returns the starting position of the first occurrence of a pattern in a specified expression. You can use wildcard characters in the expression you are looking for.

9. Wildcard characters

These are some examples of the wildcards you can use in the PATINDEX() function. You are all familiar with the % sign, which allows you to match any string of any length (including zero length). The underline allows you to match on a single character. If you want to match any of the characters in a list, you should include them in square brackets.

10. PATINDEX() example

Here are some examples of how you can use this function for looking for a specific pattern in a string. The first call of the PATINDEX() function is similar to CHARINDEX(). In the second call, we look for words that contain the "ch" combination of letters, followed by a single other character which is then followed by the letter "c". So if the words "chic" or "Chicago" were present in the sentence, they would have been taken into account.

11. Let's practice!

OK, now let's see what you've learned so far by doing some practical exercises.