BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server String Index Functions

The forty-fifth part of the SQL Server Programming Fundamentals tutorial continues the discussion of Transact-SQL (T-SQL) string processing functions. This instalment considers functions that read or modify character data at a specific index position.

String Indexing Functions

Transact-SQL (T-SQL) includes several functions that allow you to interrogate or modify the contents of character data types using character positions, or indexes. In this article we will examine four such functions. Three are used to obtain information about a string or its contents and one modifies the information held.

Len

The Len function is the simplest of those that this article will consider. The function accepts a single parameter, which must be of a character data type. It returns an integer value that expresses the length of the input string in characters. When using the large data types such as VarChar(MAX) or NVarChar(MAX), the return value will be a BigInt. For smaller types, the return value is an Int.

In the sample code below, a variable length character is declared and initialised. The length of the contained string is then outputted. NB: Use the same declaration and initialisation code for each of the sample scripts in this article.

DECLARE @string VARCHAR(50)
SET @string = 'The quick brown fox jumps over the lazy dog.'

PRINT len(@string)  -- 44

CharIndex

The CharIndex function allows you to search for a specified string within the contents of another character type. If found, the function returns the index of the match. This function can be used in two ways. The simpler method requires that you provide two arguments. The first argument is the string that you wish to locate. The second is the string that you wish to search within.

Using the same string declaration as in the previous example, we can demonstrate the function by executing the following. The returned value is one, indicating that the word "the" appears at the first index of the searched data. NB: This result is returned on a SQL Server system that is using a case-insensitive collation. If you have a case-sensitive collation option specified, the result will differ.

PRINT charindex('the', @string)     -- 1

If the first string cannot be found within the contents of the second, the function returns zero:

PRINT charindex('slow', @string)    -- 0

The second variation of the CharIndex function introduces a third parameter. This allows you to specify the index at which you wish to start the search. We can use this value to skip the first occurrence of the word "the", which we know is at position 1, by setting the start position to index 2.

PRINT charindex('the', @string, 2)  -- 32

PatIndex

The PatIndex function is similar to the simpler form of CharIndex. It allows you to locate one string within the contents of another. However, unlike CharIndex, the PatIndex function allows the search term to include wildcards. The function then returns the index of the first element of the searched data that matches the specified pattern.

You can use any of the wildcard characters and codes that were previously described for the LIKE clause. You should include a % wildcard at the left and right of the search text unless you specifically wish to only locate a pattern at the start or end of the text being searched.

The following script shows several examples of the use of the function.

PRINT patindex('the%', @string)         -- 1
PRINT patindex('%q___k%', @string)      -- 5
PRINT patindex('%f[aeiou]x%', @string)  -- 17
PRINT patindex('%dog.', @string)        -- 41

Stuff

The last function to be described in this article is named, "Stuff". This function allows you to replace some characters from a string with alternative text. The function requires four arguments. The first is the text that you wish to modify. The second and third parameters accept the index of the first character to be replaced and the number of characters that will be removed from the original string respectively. The final parameter is the new text to be inserted.

The following example removes five characters from the sample sentence, starting from position five. It replaces this text with an alternative string. Note that the inserted string does not need to be the same length as the text it replaces.

PRINT stuff(@string, 5, 5, 'slow')  -- The slow brown fox jumps over the lazy dog.
21 November 2009