Problem solve Get help with specific problems with your technologies, process and projects.

Displaying numeric data as character

With a table in Oracle 8i having values:


How to formulate the SQL such that it gives results as follows:

10 years
11 years

In other words, how do I append text value to the result? Thanks.

What you have to do is convert the numeric field to character, and then concatenate a literal string. The SQL standard function for performing datatype conversions is CAST, which has the general format

CAST ( expression AS datatype )

So in your case, you could use CAST to convert your values to a 2-byte VARCHAR2 character string, and then concatentate the literal ' years' to get the result you want --

select CAST(yourField AS VARCHAR2(2))||' years'
  from yourTable

However, this might present a problem if you have 3-digit values. But if you use VARCHAR2(3), will the values 10 and 11 have a leading zero or space? Luckily, most databases implement additional, more flexible functions. In Oracle you can use the TO_CHAR function, which will convert a number into a VARCHAR2 string that is just long enough to hold the number --

select TO_CHAR(yourField)||' years' 
  from yourTable

TO_CHAR is actually a very powerful function, because it lets you specify a format for the conversion, which can be used to display currency amounts with dollar sign, commas, and decimal point.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.