Menu
  • Text Function
    • CONCAT()
    • CHAR_LENGTH()
    • CHARACTER_LENGTH()
    • BYTE_LENGTH()
    • UPPER()
    • LOWER()
    • INITCAP
    • SUBSTR()
    • SUBSTRING()
    • REPLACE()
    • TRIM()
    • REVERSE()
    • STRPOS()
    • SPLIT()
  • Operators
    • PIVOT
  • Drivers
    • ODBC Driver for SQLite
August 15, 2022September 14, 2022

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_idorder_date_substrorder_date_substring
&&&4592022070101&&&2022070120220701
&&&4592022070102&&&2022070120220701
&&&4922022070101&&&2022070120220701
&&&4922022071201ć&&2022071220220712
&&&4822022071501&&&2022071520220715

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”.

idcustomercustomer_renamed
1Jones & Son, Inc.Jones & Jones & Son, Inc.
2Jones & Son, Inc.Jones & Jones & Son, Inc.
3Stavros GmbHStavros GmbH
4Hendricks, Hendricks, HoekstraHendricks, Hendricks, Hoekstra
5Mckenna & PartnersMckenna & Partners

Articles

  • ODBC Driver for SQLite
  • SQL Server PIVOT operator
  • Text Function – SPLIT()
  • Text Function – TRIM() , REVERSE() and STRPOS()
  • Text Function – SUBSTR(), SUBSTRING() and REPLACE()
©2023