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.
The first order was placed on 1 July 2022, according to the results.
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”.
|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|