SQL Server Text Data Manipulation

Posted by Sandeep Singh Shekhawat Articles | SQL November 02, 2012
In this article I explain most of the SQL Server functions for manipulating text.
Reader Level:

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

    img1.jpg
    Output

    img2.jpg

  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

    img3.jpg
    Output
      img4.jpg

  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

    img5.jpg
    Output
    img6.jpg
     
  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

    img7.jpg

    Output


    img8.jpg
     
  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

    img9.jpg

    Output

    img10.jpg
     
  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

    img11.jpg

    Output

    img12.jpg
     
  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

    img13.jpg
    Output
    img14.jpg
     
  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
    img15.jpg
    Output

    img16.jpg
     
  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

    img17.jpg

    Output

    img18.jpg
     
  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

    img19.jpg

    Output

    img20.jpg
     
  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

    img21.jpg

    Output


      img 22.jpg

  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

    img 23.jpg

    Output


      img 24.jpg

  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

    img 25.jpg

    Output


      img 26.jpg

  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

    img27.jpg

    Output

      img 28.jpg

  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

    img 29.jpg

    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

    img 30.jpg

    Example

    img 31.jpg

    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

      img 32.jpg

  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

    img 33.jpg

    Output

      img 34.jpg

  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

    img 35.jpg

    Output


    img 36.jpg

COMMENT USING