The forty-fifth part of the SQL Server Programming Fundamentals tutorial sentory.vnntinues the discussion of Transact-SQL (T-SQL) string processing functions. This instalment sentory.vnnsiders functions that read or modify character data at a specific index position.
Đang xem: Performance
String Indexing Functions
Transact-SQL (T-SQL) includes several functions that allow you to interrogate or modify the sentory.vnntents 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 sentory.vnntents and one modifies the information held.
The Len function is the simplest of those that this article will sentory.vnnsider. 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 sentory.vnde below, a variable length character is declared and initialised. The length of the sentory.vnntained string is then outputted. NB: Use the same declaration and initialisation sentory.vnde for each of the sample scripts in this article.
The CharIndex function allows you to search for a specified string within the sentory.vnntents 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 sesentory.vnnd 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 sentory.vnllation. If you have a case-sensitive sentory.vnllation option specified, the result will differ.
The sesentory.vnnd 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.
The PatIndex function is similar to the simpler form of CharIndex. It allows you to locate one string within the sentory.vnntents 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 sentory.vndes 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.
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 sesentory.vnnd 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.
.NET FrameworkAlgorithms and Data StructuresAudioC# Programmingsentory.vnnfigurationDebuggingDesign PatternsDocumentationGraphicsInput / OutputLINQNetwork and InternetParallel and AsynchronousPerformanceProgramming sentory.vnnceptsRefactoringReference SheetsReflectionRegular ExpressionsSecuritySQL ServerSystem InformationTestingToolsVisual StudioWindows ProgrammingWindows Presentation FoundationXML