SQL Strings and Substring Functions

In SQL, string functions are used to manipulate or return information about character or string data. They are a fundamental part of developing efficient and effective database queries. Here are some of the most commonly used string functions in SQL:

1. CONCAT(): This function combines two or more strings into one string. Its usage is as follows:
   

   SELECT CONCAT('Hello', ' World');
   

   
   This will return Hello World.
2. LENGTH(): The LENGTH function returns the length of a string in characters. For
   

   SELECT LENGTH('Hello World');
   

   
   This will return 11.
3. SUBSTRING(): The SUBSTRING function extracts a substring from a string. It accepts three arguments: the string, the start position, and the length of the substring. Here is an example:
   

   SELECT SUBSTRING('Hello World', 7, 5);
   

   
   This will return World.
4. UPPER() and LOWER(): These functions convert a string to uppercase and lowercase, respectively. Here is how they are used:
   

   SELECT UPPER('Hello World');
   SELECT LOWER('Hello World');
   

   
   The first one will return HELLO WORLD, while the second one will return hello world.
5. TRIM(): This function removes leading and trailing spaces from a string. Example usage:
   

   SELECT TRIM('  Hello World  ');
   

   
   This will return Hello World, with the leading and trailing spaces removed.
6. REPLACE(): This function replaces all occurrences of a specified string with another string. It accepts three arguments: the original string, the substring to replace, and the new substring. Here is an example:
   

   SELECT REPLACE('Hello World', 'World', 'SQL');
   

   
   This will return Hello SQL.
7. CHARINDEX(): This function returns the position of a specified string within another string. It accepts two arguments: the substring and the original string. Here is an example:
   

   SELECT CHARINDEX('World', 'Hello World');
   

   
   This will return 7, because 'World' starts at the 7th character of 'Hello World'.
8. LTRIM() and RTRIM(): These functions remove leading and trailing spaces from a string, respectively.
   

   SELECT LTRIM('  Hello World');
   SELECT RTRIM('Hello World  ');
   

   
   The first one will return Hello World with leading spaces removed, and the second one will return Hello World with trailing spaces removed.

These are just a few examples of the string functions available in SQL. The precise functions and their names can vary somewhat between different SQL dialects, but most modern SQL dialects support these functions or similar ones. Remember, these functions can be used in various parts of an SQL statement, including the SELECT, WHERE, and ORDER BY clauses, among others.

When you add two strings together, you concatenate them. This is helpful if you want to combine the first and last name fields to make the results read more easily, for example. You use this capability by simply using the plus sign (+) between the values you want to place together:

SELECT au_Fname + '' + au_Lname 
FROM Authors

Depending on the engine, if you use quotes with no spaces between them, the engine will automatically insert a space for you.

• Substrings
  Substring capabilities let you extract a portion of a string, for example, only the leftmost three characters or the middle four characters. When you use SUBSTRING, you simply indicate the information item to use, the start point, and the length to extract. For example, if you want to get the first three characters of the au_Lname column, the following statement would do the trick:
  

  SELECT SUBSTRING(au_Lname, 1, 3) 
  FROM Authors
  

  
  This will result in a new (unnamed) column containing only those first three characters.
• Substring from String:
  Along with inserting characters into a string, you may have a need to extract a substring from a string. For this purpose, all three servers include the substring() function (although Oracle Database's version is called substr()), which extracts a specified number of characters starting at a specified position. The following example extracts five characters from a string starting at the ninth position.
  

  mysql> SELECT SUBSTRING('goodbye cruel world', 9, 5);
  +----------------------------------------+
  | SUBSTRING('goodbye cruel world', 9, 5) |
  +----------------------------------------+
  | cruel |
  +----------------------------------------+
  1 row in set (0.00 sec)
  

Go to the exercise to see how these functions work.
String Containing Query - Exercise