Functions for positions in SQL

The functions that return the position of a string are:

• LEN()

• CHARINDEX()

• PATINDEX()

LEN()

• Returns the number of character of the provided string

Syntax

LEN(character_expression)

CHARINDEX

• Looks for a character expression in a given string

• returns the starting position

Syntax

CHARINDEX(expression_to_find, expressiont_to_search, [,start_location])

PATINDEX

• Similar to CHARINDEX

• Returns the starting point of a pattern in an expression

• You could use wildcards in the expression you are looking for

PATINDEX('%pattern%', expression, location)

SELECT 
    first_name,
    last_name,
    email 
FROM voters
-- Look for the "dan" expression in the first_name
WHERE CHARINDEX('dan', first_name) > 0 
    -- Look for last_names that do not contain the letter "z"
    AND CHARINDEX('z', last_name) = 0;

SELECT 
    first_name,
    last_name,
    email 
FROM voters
-- Look for first names that start with C and the 3rd letter is r
WHERE PATINDEX('C_r%', first_name) > 0;

SELECT 
    first_name,
    last_name,
    email 
FROM voters
-- Look for first names that contain one of the letters: "x", "w", "q"
WHERE PATINDEX('%[xwq]%', first_name) > 0;

Functions for string transformation

LOWER(character_expression) converts all character from string to lowercase

UPPER(character_expression) converts all character from string to uppercase

SELECT 
    company,
    bean_type,
    broad_bean_origin,
    -- 'company' and 'broad_bean_origin' should be in uppercase
    'The company ' +  UPPER(company) + ' uses beans of type "' + bean_type + '", originating from ' + UPPER(broad_bean_origin) + '.'
FROM ratings
WHERE 
    -- The 'broad_bean_origin' should not be unknown
    LOWER(broad_bean_origin) NOT LIKE '%unknown%'
     -- The 'bean_type' should not be unknown
    AND LOWER(bean_type) NOT LIKE '%unknown%';

LEFT(character_expression, number_of_characters) It returns the specified number of character from the beginning of the string

RIGHT(character_expression, number_of_characters) It returns the specified number of character from the end of the string

SELECT 
    first_name,
    last_name,
    country,
    -- Select only the first 3 characters from the first name
    LEFT(first_name, 3) AS part1,
    -- Select only the last 3 characters from the last name
    RIGHT(last_name, 3) AS part2,
    -- Select only the last 2 digits from the birth date
    RIGHT(birthdate,2) AS part3
FROM voters;

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(character_expression, searched_expression, replacement_expression) returns a string where all occurrence of an expression are replaced with another

SELECT 
    first_name,
    last_name,
    email,
    -- Replace "yahoo.com" with "live.com"
    REPLACE(email, 'yahoo.com', 'live.com') AS new_email
FROM voters;

SUBSTRING(character_expression, start, number_of_character) returns part of a string

SELECT 
    email,
    -- Extract 5 characters from email, starting at position 3
    SUBSTRING(email, 3 ,5) AS some_letters
FROM voters;

DECLARE @sentence NVARCHAR(200) = 'Apples are neither oranges nor potatoes.'
SELECT
    -- Extract the word "Apples" 
    SUBSTRING(@sentence, 1, 6) AS fruit1,
    -- Extract the word "oranges"
    SUBSTRING(@sentence, 20, 7) AS fruit2;

CONCAT() and CONCAT_WS()

CONCAT(string1,string2 [,stringN]) allows you to concatenate strings

DECLARE @string1 NVARCHAR(100) = 'Chocolate with beans from';
DECLARE @string2 NVARCHAR(100) = 'has a cocoa percentage of';

SELECT 
    bean_type,
    bean_origin,
    cocoa_percent,
    -- Create a message by concatenating values with "+"
    @string1 + ' ' + bean_origin + ' ' + @string2 + ' ' + CAST(cocoa_percent AS nvarchar) AS message1,
    -- Create a message by concatenating values with "CONCAT()"
    CONCAT(@string1, ' ', bean_origin, ' ', @string2, ' ', CAST(cocoa_percent AS nvarchar)) AS message2
FROM ratings
WHERE 
    company = 'Ambrosia' 
    AND bean_type <> 'Unknown';

CONCAT_WS(separator, string1, string2[,stringN]) allows you to concatenate strings with a separator

DECLARE @string1 NVARCHAR(100) = 'Chocolate with beans from';
DECLARE @string2 NVARCHAR(100) = 'has a cocoa percentage of';

SELECT 
    bean_type,
    bean_origin,
    cocoa_percent,
    -- Create a message by concatenating values with "+"
    @string1 + ' ' + bean_origin + ' ' + @string2 + ' ' + CAST(cocoa_percent AS nvarchar) AS message1,
    -- Create a message by concatenating values with "CONCAT()"
    CONCAT(@string1, ' ', bean_origin, ' ', @string2, ' ', cocoa_percent) AS message2,
    -- Create a message by concatenating values with "CONCAT_WS()"
    CONCAT_WS(' ', @string1, bean_origin, @string2, cocoa_percent) AS message3
FROM ratings
WHERE 
    company = 'Ambrosia' 
    AND bean_type <> 'Unknown';

STRING_AGG(expression, separator)[order clause] concatenates the values of string expressions and places separator values between them.

SELECT
    -- Create a list with all bean origins, delimited by comma
    STRING_AGG(bean_origin, ',') AS bean_origins
FROM ratings
WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters');

SELECT 
    company,
    -- Create a list with all bean origins
    STRING_AGG(bean_origin, ',') AS bean_origins
FROM ratings
WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters')
-- Specify the columns used for grouping your data
GROUP BY company;

SELECT 
    company,
    -- Create a list with all bean origins ordered alphabetically
    STRING_AGG(bean_origin, ',') WITHIN GROUP (ORDER BY bean_origin) AS bean_origins
FROM ratings
WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters')
-- Specify the columns used for grouping your data
GROUP BY company;

STRING_SPLIT(string,operator) divides a string into smaller pieces, based on the separator. Also, it returns a single column table.

DECLARE @phrase NVARCHAR(MAX) = 'In the morning I brush my teeth. In the afternoon I take a nap. In the evening I watch TV.'

SELECT value
FROM STRING_SPLIT(@phrase, '.');

DECLARE @phrase NVARCHAR(MAX) = 'In the morning I brush my teeth. In the afternoon I take a nap. In the evening I watch TV.'

SELECT value
FROM STRING_SPLIT(@phrase, ' ');

RECAP

SELECT
    first_name,
    last_name,
    birthdate,
    email,
    country
FROM voters
   -- Select only voters with a first name less than 5 characters
WHERE LEN(first_name) < 5
   -- Look for the desired pattern in the email address
    AND PATINDEX('j_a%@yahoo.com', email) > 0;

SELECT
    -- Concatenate the first and last name
    CONCAT('***' , first_name, ' ', upper(last_name), '***') AS name,
    last_name,
    birthdate,
    email,
    country
FROM voters
   -- Select only voters with a first name less than 5 characters
WHERE LEN(first_name) < 5
   -- Look for this pattern in the email address: "j%[0-9]@yahoo.com"
    AND PATINDEX('j_a%@yahoo.com', email) > 0;

SELECT
    -- Concatenate the first and last name
    CONCAT('***' , first_name, ' ', UPPER(last_name), '***') AS name,
    -- Mask the last two digits of the year
    REPLACE(birthdate, SUBSTRING(CAST(birthdate AS varchar), 3, 2), 'XX') AS birthdate,
    email,
    country
FROM voters
   -- Select only voters with a first name less than 5 characters
WHERE LEN(first_name) < 5
   -- Look for this pattern in the email address: "j%[0-9]@yahoo.com"
    AND PATINDEX('j_a%@yahoo.com', email) > 0;