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.