Text Function – SUBSTR(), SUBSTRING() and REPLACE()

Two functions named SUBSTR() and SUBSTRING() perform exactly the same action. Both are intended to condense a string into a particular substring at a given position and extend it by a specific number of characters.

SELECT order_id,
       SUBSTR(order_id, 7, 8) AS order_date_substr,
       SUBSTRING(order_id, 7, 8) AS order_date_substring
FROM `orders.orders`; 

The first step in transforming a string is to extract a portion of the string. The column order_id is what is used in this example. The second argument is the starting position of the first argument. If the number is greater than zero then it is positive.

The substring starts from the fourth character of the string, counting from the left in the above example. The length of the string is required to make an argument. In this case the length of the substring is eight. 

Why am I able to determine what order ID I received with this method of determining it? If you know the logic behind order IDs, then you will see that the seventh character of the order ID contains the information about the date the order was placed.

Yes, the order ID contains the second quarter character of the eight characters (year, month, date) and then begins with the seventh character.

order_id	order_date_substr	order_date_substring
&&&4592022070101&&&	20220701	20220701
&&&4592022070102&&&	20220701	20220701
&&&4922022070101&&&	20220701	20220701
&&&4922022071201ć&&	20220712	20220712
&&&4822022071501&&&	20220715	20220715

The first order was placed on 1 July 2022, according to the results.

REPLACE()

The REPLACE() function can be used to replace a string of characters with another string. For example, a company called Jones & Son, Inc. was renamed to Jones & Jones & Son, Inc. Rename these occurrences in the table

SELECT id,
       customer,
       REPLACE(customer, 'Son', 'Jones & Son') AS customer_renamed
FROM `orders.orders`;

In the function, I first specify the column I want to change. Okay, we’ll use “Jones & Son” instead of “Son”.

id	customer	customer_renamed
1	Jones & Son, Inc.	Jones & Jones & Son, Inc.
2	Jones & Son, Inc.	Jones & Jones & Son, Inc.
3	Stavros GmbH	Stavros GmbH
4	Hendricks, Hendricks, Hoekstra	Hendricks, Hendricks, Hoekstra
5	Mckenna & Partners	Mckenna & Partners