String Functions in SQL

String Functions in SQL are built-in functions that allow users to manipulate character data in various ways. These functions can perform tasks such as formatting text, extracting substrings, and searching for specific patterns within a string.

Here are some standard String Functions in SQL:

• CONCAT: Concatenates two or more strings together.
• SUBSTRING: Extracts a substring from a larger string.
• LENGTH: Returns the length of a string.
• LOWER: Converts a string to lowercase.
• UPPER: Converts a string to uppercase.
• TRIM: Removes whitespace or specified characters from the beginning or end of a string.
• REPLACE: Replaces one or more instances of a substring within a string.
• INSTR: Searches for the position of a substring within a string.

CONCAT

Suppose we have a table named customers with columns firstName and lastName. We want to create a new column called full_name that combines each customer’s first and last names into a single string.

Here’s the basic syntax:

SELECT CONCAT(firstName, ' ', lastName) AS fullName
FROM bookshop_customer;

The result is a new column called “fullName” that contains the concatenated strings.

Note that the CONCAT function can take any number of string values as input, and it will concatenate them in the order they are specified in the function. The CONCAT function will return a null value if any input value is null.

SELECT SUBSTRING(lastName, 1, 3) AS firstName
FROM bookshop_customer; 

SUBSTRING

The SUBSTRING SQL string function extracts a substring or a portion of a string from a larger string. It takes three arguments: the string to be removed from, the substring’s starting position, and the substring’s length. We can use the SUBSTRING function in our SQL query:

SELECT SUBSTRING(lastName, 1, 3) AS firstName
FROM bookshop_customer;

In this example, the SUBSTRING function extracts the first three characters of the “lastName” column.

LOWER / UPPER

The LOWER SQL string function converts all the characters of a string to lowercase. It takes a single-string argument and returns a new string with all the characters in lowercase.

Here is an example of using the LOWER function:

We will look at the column email in the bookshop_customer table to convert all the email addresses to lowercase.

The LOWER function in our SQL query is as follows:

SELECT LOWER(email) AS lowercase_email
FROM bookshop_customer;

In this example, the LOWER function converts all the characters in the email column to lowercase. The result is a new column called lowercase_email containing lowercase email addresses.

And if you need to uppercase the rows in a column, use the same syntax with the UPPER string function.

Here’s an example:

SELECT UPPER(title) AS uppercase_name
FROM bookshop; 

Resulting table:

TRIM

The TRIM SQL string function removes leading or trailing whitespace (spaces, tabs, or newlines) from a string. It takes a single string argument and returns a new string with the leading and trailing whitespace removed.

Here is an example of using the TRIM function:

Suppose we have a table named bookshop_orders with a column named customer_name. We want to remove any leading or trailing whitespace from the customer names in the table.

We can use the TRIM function in our SQL query as follows:

SELECT TRIM(customer_name) AS trimmed_name
FROM bookshop_orders;

In this example, the TRIM function removes any leading or trailing whitespace from the customer_name column. The result is a new column called “trimmed_name” that contains the trimmed customer names.

Note that the TRIM function only removes whitespace characters from the beginning and end of the input string. If there are whitespace characters in the middle of the string, the function will not affect them. Also, if the input string is null, the TRIM function returns a null value. Additionally, there are variations of TRIM, such as LTRIM (removes only leading spaces) and RTRIM (removes only trailing spaces), that can be used depending on the requirements.

REPLACE

The REPLACE SQL string function replaces all occurrences of a specific substring in a string with a new substring. It takes three arguments: the original string, the substring to be replaced, and the new substring.

Here is an example of using the REPLACE function:

Suppose we have a table named books with a column named description. We want to replace all occurrences of the word “unnew” with the word “second_hand” in the descriptions.

We can use the REPLACE function in our SQL query as follows:

SELECT REPLACE(description, 'unnew', 'second_hand') AS new_description
FROM books;

In this example, the REPLACE function replaces all occurrences of the substring “unnew” with the substring “second_hand” in the description column. The result is a new column called “new_description” that contains the updated product descriptions.

Note that the REPLACE function is case-sensitive, so it will only replace exact matches of the substring to be replaced. If there are no occurrences of the substring to be replaced in the input string, the function will return the original string unchanged. Additionally, the REPLACE function returns a null value if arguments are null.

INSTR

The INSTR SQL string function is used to find the position of a substring within a string. It takes two arguments: the original string and the substring to search for.

Here is an example of using the INSTR function:

Suppose we have a table named orders with a column named shipping_address. We want to find the position of the comma (“,”) in the shipping address to extract the city from the full address.

We can use the INSTR function in our SQL query:

SELECT shipping_address, INSTR(shipping_address, ',') AS comma_position
FROM orders;

In this example, the INSTR function searches for the comma (“,”) in the shipping_address column and returns the position of the first occurrence of the comma in each address. The result is a new column called comma_position that contains the position of the comma in each shipping address.

Note that the position returned by the INSTR function is a 1-based index, meaning the string’s first character is at position 1. If the substring is not found in the input string, the INSTR function returns 0. Additionally, the INSTR function returns a null value if either argument is null.

Power Up String Analysis with CoginitiScript

In Coginiti, you can create CoginitiScript macros to standardize string manipulations across your SQL queries. For instance, create a macro to abstract the SPLIT_PART function to extract a specific substring pattern:

#+macro ExtractDomain(email)
#+begin
  SPLIT_PART('{{email}}', '@', 2)
#+end

SELECT 
  {{ ExtractDomain('example@coginiti.com') }} AS Domain
FROM Customers;

Conclusion

Mastering string functions in SQL is crucial for effective data analysis. By utilizing these functions and leveraging CoginitiScript for standardized operations, you can achieve more dynamic and efficient data manipulation. Embrace the power of SQL and try free Coginiti to revolutionize your data analysis journey.