{"id":1812,"date":"2025-02-02T09:55:17","date_gmt":"2025-02-02T02:55:17","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1812"},"modified":"2025-02-10T15:08:05","modified_gmt":"2025-02-10T08:08:05","slug":"postgresql-upper","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-string-functions\/postgresql-upper\/","title":{"rendered":"PostgreSQL UPPER Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL UPPER<\/code> function to convert all characters in a string to uppercase.<\/p>\n\n\n\n

Introduction to the PostgreSQL UPPER function #<\/a><\/h2>\n\n\n\n

The UPPER<\/code> function takes a string and returns a new string with all characters converted to uppercase.<\/p>\n\n\n\n

Here’s the syntax of the UPPER<\/code> function:<\/p>\n\n\n

UPPER(\u00a0text<\/span>\u00a0) \u2192\u00a0text<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The UPPER<\/code> function takes a parameter string with type text and returns a value with the type text with all characters converted to uppercase.<\/p>\n\n\n\n
Since TEXT<\/code>, VARCHAR<\/code>, and CHAR<\/code>are binary coercible,<\/strong> PostgreSQL implicitly casts them to TEXT<\/code>, so you don’t have to perform an explicit conversion.<\/p>\n\n\n\n
The UPPER<\/code> function returns NULL<\/code> if the input string is NULL<\/code>.<\/p>\n\n\n\n
Note that the UPPER<\/code> function does not modify the original string but returns a new string.<\/p>\n\n\n\n
Basic UPPER function examples #<\/a><\/h2>\n\n\n\n
The following example uses the UPPER<\/code> function to convert all the characters in the string \"pgtutorial.com\"<\/code> to uppercase:<\/p>\n\n\n
SELECT<\/span>\n  UPPER('pgtutorial.com'<\/span>) result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     result\n----------------<\/span>\n PGTUTORIAL.COM<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement returns NULL<\/code> because the input string is NULL<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  UPPER(NULL<\/span>) result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Converting table data to uppercase #<\/a><\/h2>\n\n\n\n
Let’s use the products<\/code> table from the inventory<\/code> database:<\/p>\n\n\n\n
\"PostgreSQL<\/figure>\n\n\n\n
The following query uses the UPPER<\/code> function to convert the product names from the products<\/code> table to uppercase:<\/p>\n\n\n
SELECT<\/span>\n  UPPER(product_name) formatted_product_name\nFROM<\/span>\n  products\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   formatted_product_name\n----------------------------<\/span>\n APPLE AIRPODS PRO 3<\/span>\n APPLE IMAC 24<\/span>\"\n APPLE IPAD PRO 12.9\n APPLE IPHONE 15\n...<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Matching data case-insensitively #<\/a><\/h2>\n\n\n\n
When matching data using the equal to operator, PostgreSQL matches it case-insensitively.<\/p>\n\n\n\n
For example, if you search for a product with the name \"APPLE IPHONE 15\"<\/code>, PostgreSQL will return an empty set:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price\nFROM<\/span>\n  products\nWHERE<\/span>\n  product_name = 'APPLE IPHONE 15'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To match data case-insensitively, you can use the UPPER<\/code> function in the WHERE<\/a><\/code> clause as follows:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price\nFROM<\/span>\n  products\nWHERE<\/span>\n  UPPER(product_name) = 'APPLE IPHONE 15'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output<\/p>\n\n\n
  product_name   |  price\n-----------------+---------<\/span>\n Apple iPhone 15<\/span> | 1099.99<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the UPPER<\/code> function to convert all characters of a string to uppercase.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
If you pass a value of a type other than TEXT<\/a><\/code>, VARCHAR<\/a><\/code>, and CHAR<\/a><\/code>, you must explicitly cast it to the text type.<\/p>\n\n\n\n