Home arrow Oracle arrow Retrieving Data with String and Arithmatic Expressions

Retrieving Data with String and Arithmatic Expressions

In this third part of a nine-part series on retrieving data from tables with the SELECT statement, you will learn how to code both string and arithmetic expressions. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

  1. Retrieving Data with String and Arithmatic Expressions
  2. How to code arithmetic expressions
By: Murach Publishing
Rating: starstarstarstarstar / 0
July 28, 2011

print this article



How to code string expressions

A string expression consists of a combination of one or more character columns and literal values. To combine, or concatenate, the columns and values, you use the concatenation operator (||). This is illustrated by the examples in figure 3-5.

The first example shows how to concatenate the vendor_city and vendor_state columns in the Vendors table. Notice that because no alias is assigned to this column, Oracle assigns a name, which is the entire expression. Also notice that the data in the vendor_state column appears immediately after the data in the vendor_city column in the results. Thatís because of the way vendor_city is defined in the database. Because itís defined as a variable-length column (the VARCHAR2 data type), only the actual data in the column is included in the result. In contrast, if the column had been defined with a fixed length, any spaces after the name would have been included in the result. Youíll learn about data types and how they affect the data in your result set in chapter 8.

The second example shows how to format a string expression by adding spaces and punctuation. Here, the vendor_city column is concatenated with a string literal, or string constant, that contains a comma and a space. Then, the vendor_state column is concatenated with that result, followed by a string literal that contains a single space and the vendor_zip_code column.

Occasionally, you may need to include a single quotation mark or an apostrophe within a literal string. If you simply type a single quote, however, the system will misinterpret it as the end of the literal string. As a result, you must code two single quotation marks in a row. This is illustrated by the third example in this figure.

How to concatenate string data

SELECT vendor_city, vendor_state, vendor_city || vendor_state
FROM vendors

How to format string data using literal values

SELECT vendor_name,
vendor_city || ', ' || vendor_state || ' ' ||
vendor_zip_code AS address
FROM vendors

How to include apostrophes in literal values

SELECT vendor_name || '''s address: ',
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code
FROM vendors


  1. A string expression can consist of one or more character columns, one or more literal values, or a combination of character columns and literal values.
  2. The columns specified in a string expression must contain string data (that means theyíre defined with the CHAR or VARCHAR2 data type).
  3. The literal values in a string expression also contain string data, so they can be called string literals or string constants. To create a literal value, enclose one or more characters within single quotation marks (').
  4. You can use the concatenation operator (||) to combine columns and literals in a string expression.
  5. You can include a single quote within a literal value by coding two single quotation marks, as shown in the third example above.

--------------------------------------------Figure 3-5 How to code string expressions

>>> More Oracle Articles          >>> More By Murach Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: