SQL Server Text Data Manipulation

Introduction

In this article I explain most of the SQL Server functions for manipulating text.

The list of functions explained in this article is:

Sr. No	Function	Sr. No	Function
1.	CHARINDEX	10.	REVERSE
2.	PATINDEX	11.	REPLACE
3.	LEFT	12.	STUFF
4.	RIGHT	13.	REPLICATE
5.	LEN	14.	QUOTENAME
6.	LTRIM	15.	STR
7.	RTRIM	16.	SUBSTRING
8.	LOWER	17.	SPACE
9.	UPPER

Explanation of Functions

Here I am explaining each function mentioned in the above table.

1. CHARINDEX - We can use the CHARINDEX function to search text in a string expression. It takes three arguments. It returns the first occurrence of text being searched for in another text expression or returns 0 if there is not a match.
   Syntax
   
   CHARINDEX(searchExperision, textExpresion, startIndex)
   
   The following explains each argument:
   
   
   • textExpresion: It is the whole text data being searched for a text part. It is a character expression. It is a required parameter and we need to pass it to the function because if we don't pass it then we get 0 from the function as output. But in textExpresion we cannot use more than 8000 characters.
   • searchExperision : it is the part of text which we want to search for in textExpresion. It is a character expression. It is a required argument and we need to pass it to the function because if we don't pass it then we get 0 from the function as output.
   • startIndex : It is an int or bigint value that is used to define the start index, in other words an index value in textExpresion where the search will start for searchExperision. It is optional, in other words if we do not pass an argument start index to the CHARINDEX function then the index starts from 0. But we whether do or do not pass a start index value to the CHARINDEX function, it always returns a zero-based index value.
     
     
   Example
   
   
   Output
   
   
   
   
2. PATINDEX : We can use the PATINDEX function to search text in a string expression. It takes two arguments. It returns the first occurrence of a text pattern being searched for in another text expression or returns 0 if there is not a match. In this function we use a wild card to search a character expression.
   
   Syntax:
   
   PATINDEX(searchExperision, textExpresion)
   
   The following explains each parameter:
   
   
   • textExpresion : It is the whole text data to be searched for a a text pattern. It is a character expression. It is a required parameter and we need to pass it to the function because if we do not pass it then the expression will give an error instead of 0.
   • searchExperision : it is a part of text which we want to search for in textExpresion. It is a character expression. It is a required parameter and we need to pass it to the function because if we don't pass it then we get an error from the function instead of 0. This contains a wild card character.
     
     
   Example
   
   
   Output
    
   
   
3. LEFT : It returns text from the left portion of the character string with a fixed number of characters. It takes two parameters.
   
   Syntax:
   
   LEFT(textExpression, noOfChar)
   
   The following explains each argument:
   
   
   • textExpression : It is the whole text data where we can get a text part. It is a character expression. It is a required parameter and we need to pass it to the function.
   • noOfChar : It's an integer value which is the length of the character string. That will be calculated from the left side.
     
     
   Example
   
   
   Output
   
    
4. RIGHT : It returns text from the right portion of the character string with a fixed number of characters. It takes two arguments.
   
   Syntax:
   
   RIGHT(textExpression, noOfChar)
   
   The following explains each argument:
   
   
   • textExpression : It is the whole text data where we can get a text part. It is a character expression. It is a required argument and we need to pass it to the function.
   • noOfChar : it's an integer value which is the length of the character string which is returned from the right side of textExpression.
     
     
   Example
   
   
   
   Output
   
   
    
5. LEN : It returns the number of characters of the character expression. It excludes the right side (trailing) blanks but does not exclude the left side (leading) blanks when counting the length. It takes one argument.
   
   Syntax
   
   LEN(textExpression)
   
   The following explains the argument:
   
   
   • textExpression : It is the whole text data where we can get the total number of characters. It can be a character expression, constant or column. It can also be an int data type. It is a required parameter and we need to pass it to the function.
     
     
   Example
   
   
   
   Output
   
   
    
6.  LTRIM : It returns a character expression after removing left-side (trailing) blanks. It takes one argument and returns a character expression.
   
   Syntax:
   
   LTRIM(textExpression)
   
   The following explains the argument:
   
   
   • textExpression : It is the whole text data by which we can get a character expression. It can be a character expression, constant or column. It is a required parameter and we need to pass it to the function.
     
     
   Example
   
   
   
   Output
   
   
    
7. RTRIM : It returns a character expression after removing right-side (trailing) blanks. It takes one argument and returns a character expression.
   
   Syntax:
   
   RTRIM(textExpression)
   
   The following explains the argument:
   
   
   • textExpression : It is the whole text data by which we can get character expression. It can be character expression, constant or column. It is a required parameter and we need to pass it to the function.
     
     
   Example
   
   
   Output
   
    
8. LOWER: It returns a character expression. It converts all upper-case characters to lower-case. It takes one argument as a character expression which will be converted to lower-case characters.
   
   Syntax:
   
   LOWER(textExpression)
   
   The following explains the argument:
   
   
   • textExpression : It is the whole text data to be converted to lower-case. It can be a character expression, constant or column. It is a required argument and we need to pass it to the function.
     
     
   Example
   
   Output
   
    
9. UPPER: It returns a character expression. It converts all lower-case characters to upper-case. It takes one argument as a character expression which will be converted to upper-case characters.
   
   Syntax:
   
   UPPER(textExpression)
   
   The following explains the argument:
   
   
   • textExpression : It is the whole text data to be converted to upper-case. It can be a character expression, constant or column. It is a required argument and we need to pass it to the function.
     
     
   Example
   
   
   
   Output
   
   
    
10. REVERSE: It returns a character expression. This function reverses a character expression; in other words it reverses the whole statement with each word. It not only reverses the character expression but also can reverse integer values. It takes one argument.
    
    Syntax:
    
    REVERSE(textExpression)
    
    The following explains the argument:
    
    
    • textExpression : It is the whole text data by which we can get the character expression in reverse. It can be a character expression, constant or column. It is a required argument and we need to pass it to the function.
      
      
    Example
    
    
    
    Output
    
    
     
11. REPLACE: it replaces a string value in another string value for all occurrences with a string value. It returns the string value after replacing and it takes three arguments.
    
    Syntax:
    REPLACE(textExpresion, findExpresion, replaceExpresion)
    
    The following explains each argument:
    
    
    • textExpresion : It is the whole text data where we replace a string value. It is a character expression. It is a required argument and we need to pass it to the function because if we don't pass it then we get an error.
    • findExpresion: it is a part of text that we want to replace in textExpresion. It is a character expression. It is a required argument and we need to pass it to the function because if we don't pass it then we get an error from the function.
    • replaceExpresion: It is a string value that is used to replace a string value in textExpresion where findExpresion occurs. t is a required argument and we need to pass it to the function because if we don't pass it then we get an error from the function.
      
      
    Example
    
    
    
    Output
    
     
    
    
12. STUFF: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. If the start position or the length is negative, a NULL string is returned. It takes four arguments.
    
    Syntax:
    
    STUFF(textExpression, startPosition, length, insertExpression)
    
    Now explains each argument:
    
    
    • textExpression: this argument is used in the STUFF function to pass the actual string expression on which a specific length string will be replaced by the new string.
    • startPosition: it is the int value where the new string will be inserted and the existing string will be deleted (of a specific length) as defined in the STUFF function.
    • lenght: it is an int value which specifies how many characters will be deleted after the start position.
    • insertExpression: it is a string value which will be inserted in textExpression.
      
      
    Example
    
    
    
    Output
    
     
    
    
13. REPLICATE: This function repeats a character expression a specific number of times, in other words we want to repeat a string value a number of times in a character expression. It takes two arguments and returns a character expression.
    
    Syntax:
    REPLICATE(textExpresion, integerExpression)
    
    The following explains each argument:
    
    
    • textExpresion : It is the whole text data which we can repeat. It is a character expression. It is a required parameter and we need to pass it to the function because if we don't pass it then we get an error. But it should not be a varchar(max) or nvarchar(max). It can be a column name, constant, string value or integer value.
      
      
    • integerExpression: This parameter specifies for how many times to repeat the character expression. It is a bigint or int value.
      
      
    Example
    
    
    
    Output
    
     
    
    
14. QUOTENAME : The QUOTENAME function appends square brackets to the beginning and end of the string expression and thereby makes a string expression a valid SQL Server identifier. It takes one argument. But when we have a closing square bracket in the string expression then QUOTENAME appends an extra closing square bracket also. The QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally it's a bad idea to use reserved words, special characters and spaces inside your object names.
    
    Syntax:
    
    QUOTENAME(textExpression)
    
    Now explain argument:
    
    
    • textExpression: It is a string expression to which square brackets are appended. If it contains a square bracket or closing square brackets also then in the return string expression closing square brackets will be appended after the square brackets.
      
      
    Example
    
    
    
    Output
    
     
    
    
15. STR: The str function converts numbers (int, bigint, float etc.) to characters. It takes three arguments for the number expression, length of the number and the number of places after the decimal point.
    
    Syntax:
    
    STR(numberExpression, length, decimalNumber)
    
    Now explains each arguments:
    
    
    • numberExpression : it is a numeric expression that contains the int, bigint or float type values. It is a required argument. It will be converted to a string using the STR function.
    • length : it is an optional argument. It defines the entire length of the returned string expression and should be long enough to accommodate the decimal point and the number's sign. The decimal portion of the result is rounded to fit within the specified length. If the integer portion of the number does not fit within the length, however, thne str returns a row of "*" asterisks of the specified length.
    • decimalNumber: It is an integer value and an optional argument that defines the number of digits after the decimal point.
      
      
    Example
    
    
    
    In the above example, length is 6 and the total numeric expression length is 7 so here the digits after the decimal point will be rounded off.
    
    Output
    
    
    
    Example
    
    
    
    In the above example length is 2 and the total numeric expression length before the decimal point is 3 so here the return value will be filled with "*" (asterisk).
    
    Output
    
     
    
    
16. SUBSTRING: It returns the portion of the character expression from a string value. It takes three arguments.
    
    Syntax:
    
    SUBSTRING(textExpression. startIndex, length)
    
    Now explain each argument:
    
    
    • textExpression: It can be a character, binary, text or image data type. It is a required argument. The SUBSTRING function returns a value from this expression.
    • startIndex : It is an int or bigint type. It is the value specifying where the returned character expression starts. If startIndex is less than 0, an error is generated and the statement is terminated. If startIndex is greater than the number of characters in the value expression, a zero-length expression is returned.
    • length: It is a positive integer or bigint type value. It defines how many characters will be returned from the start index. If it is negative then the statement will return an error.
      
      
    Example
    
    
    
    Output
    
     
    
    
17. SPACE : It repeats the space character in a string. It takes one argument that represents how many spaces will be returned.
    
    Syntax:
    
    SPACE(numericExpression)
    
    Now explain argument:
    
    
    • numericExpression : It is an int or bigint type numeric value that represents how may spaces will be added to the string value.
      
      
    Example
    
    
    
    Output