Functions for Positions

• Position Functions

  • LEN()
  • CHARINDEX()
  • PATINDEX()

• LEN()

  Returns the number of characters of the provided string.

  Syntax: LEN(character_expression)

• LEN() example - constant parameter

        SELECT
            LEN('Do you know the length of this sentence?') AS length
  

• LEN() example - table column paramter

        SELECT DISTINCT TOP 5
            bean_origin, 
            LEN(bean_origin) AS length
        FROM ratings;
  

• CHARINDEX()

  • Looks for a character expression in a givenstring.
  • Returns its starting position

  Syntax: CHARINDEX(expression_to_find, expression_to_search, [, start_location])

        SELECT
            CHARINDEX('chocolate', 'White chocoalte is not real chocolate'),
            CHARINDEX('chocolate', )
  

• PATINDEX()

  • Similar to CHARINDEX()
  • Returns the starting position of a pattern in an expression

  Syntax: PATINDEX(‘%pattern%’, expression, [location])

        SELECT
            PATINDEX('%chocolate%', 'White chocolate is not real chocolate') AS position1,
            PATINDEX('%ch_c%', 'White chocolate is not real chocolate') AS position2;
  

• Wildcard Characters

  | Wildcard | Explanation | | % | Match any string of any length (including zero length) | | _ | Match on a single character | | [] | Match on any character in the [] brackets (for example, [abc] would match on a, b, or c characters)

---

Functions for string transformation

• LOWER() and UPPER()

  • LOWER(character_expression)
    • Converts all characters from a string to lowercase.
  • UPPER(character_expression)
    • Converts all characters from a string to uppercase.

  ```sql SELECT country, LOWER(country) AS country_lowercase, UPPER(country) AS country_uppercase FROM voters;

• LEFT() and RIGHT()

  • LEFT(character_expression, number_of_characters)
    • Returns the specified number of characters from the beginning of the string
  • RIGHT(character_expression, number_of_characters)
    • Returnsthe specified number of characters from the end of the string

        SELECT
            country, 
            LEFT(country,3) AS country_prefix,
            email,
            RIGHT(email, 4) AS email_domain
        FROM voters;
  

• LTRIM(), RTRIM(), and TRIM()

  • LTRIM(character_expression)
    • Returns a string after removing the leading blanks.
  • RTRIM(character_expression)
    • Returns a string after removing the trailing blanks.
  • `TRIM([characters FROM] character_expression)
    • Returns a string after removing the blanks or other specified characters.

• REPLACE()

  Syntax: REPLACE(character_expression, searched_expression, replacement_expression)

    - Returns a string where all occurences of an expression are replaced with another one.
  

        SELECT REPLACE('I like apples, apples are good.', 'apple', 'orange') AS result;
  

• SUBSTRING()

  Syntax: REPLACE(character_expression, start, number_of_characters)

    - Returns part of a string.
  

        SELECT REPLACE('123456789', 5, 3) AS result;
  

---

Functions manipulating groups of strings

• CONCAT() and CONCAT_WS()

  • CONCAT(string1, string2, [, stringN])
  • CONCAT_WS(separator, string1, string2, [, stringN])

  Keep in mind: concatenating data with functions is better than using the “+” operator.

        SELECT
            CONCAT('Apples', 'and', 'oranges') AS result_concat,
            CONCAT_WS(' ', 'Apples', 'and', 'oranges') AS result_concat_ws,
            CONCAT_WS('***', 'Apples', 'and', 'oranges') AS result_concat_ws2;
  

• STRING_AGG()

  Syntax: STRING_AGG(expression, separator) [ <order_clause>]

  • Concatenates the values of string expressions and places separator values between them.

        SELECT
            STRING_AGG(first_name, ',') AS list_of_names
        FROM voters;
  
        SELECT
            STRING_AGG(CONCAT(first_name, ' ', last_name, ' (', first_vote_date, ')'), CHAR(13)) AS list_of_names
        FROM voters;
  

• STRING_AGG() with GROUP BY

        SELECT
            YEAR(first_vote_date) AS voting_year,
            STRING_AGG(first_name, ', ') AS voters
        FROM voters
        GROUP BY YEAR(first_vote_date);
  

• STRING_AGG() with the optional

        SELECT
            YEAR(first_vote_date) AS voting_year,
            STRING_AGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name ASC) AS voters
        FROM voters
        GROUP BY YEAR(first_vote_date);
  

• STRING_SPLIT()

  Syntax: STRING_SPLIT(string, separator)

  • Divides a string into smaller pieces, based on a separator.
  • Returns a single column table