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 10, 2022September 14, 2022

Text Function – UPPER(), LOWER() and INITCAP()

All the letters of the string are turned into the same color by the UPPER and LOWER functions. It could be either INITCAP() or something in between. The first letter of a word is the highest letter in the word. It is possible to define a separator. The first letter of each word would become a capital letter if you did that.

There is an example of a function:

SELECT customer,
       UPPER(customer) AS customer_upper,
       LOWER(customer) AS customer_lower,
       INITCAP(customer, ',') AS customer_initial_upper
FROM `orders.orders`;

The syntax for calculating the column value is the same as that of saying the column in parentheses next to the function, and you get the desired outcome. INITCAP() is indicated by calling the separator. When you have the idea in my mind is that the quotation mark. The last word is a comma.

There is a result returned by the code:

customercustomer_uppercustomer_lowercustomer_initial_upper
Jones & Son, Inc.JONES & SON, INC.jones & son, inc.Jones & son, inc.
Jones & Son, Inc.JONES & SON, INC.jones & son, inc.Jones & son, inc.
Stavros GmbHSTAVROS GMBHstavros gmbhStavros gmbh
Hendricks, Hendricks, HoekstraHENDRICKS, HENDRICKS, HOEKSTRAhendricks, hendricks, hoekstraHendricks, hendricks, hoekstra
Mckenna & PartnersMCKENNA & PARTNERSmckenna & partnersMckenna & partners

The functions work the way they are described. It is important to remember that the result of INITCAP is one string per field. Do not forget the last word. One of the most important marks in writing is the comma. It is important to know how it is used and what it does.

Actually, yes, in this case it is possible to apply the technique of punctuating a list like this. For that reason, you don’t see the effect of INITCAP(). Here is an example of how the SQL code is generated. This shows the SQL logic being generated into a temporary function which is then applied to your string. Nowadays BigQuery has an INITCAP() function which does a similar job as the above UDF, so I just wanted to show you the effect of the INITCAP() function in comparison.

CREATE TEMP FUNCTION
PROPER(str STRING) AS ((
   SELECT
     STRING_AGG(CONCAT(UPPER(SUBSTR(w,1,1)), LOWER(SUBSTR(w,2))), ' '
     ORDER BY
       pos)
   FROM
     UNNEST(SPLIT(str, ' ')) w
     WITH OFFSET
       pos ));
SELECT
 string,
     PROPER(string) AS proper_string_udf,
     INITCAP(string) AS proper_string_function,
FROM (
 SELECT
   'i love data warehouses' AS string )

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