How To Use Formatting in Oracle SQL

Perception is Key!

Once you retrieve your data, you should think about how you would like to view it. You can do a lot of formatting in Oracle SQL, which translates into saved time for you. After all who wants dirty data?

Formatting is a presentation issue. It allows you to present the data you have gathered so that it looks good. And as someone once said, "Perception is key." I use the following 8 formatting tricks. There are many more, but the ones below are essential to know.

  1. Round and/or truncate the date field to the nearest month:

    SELECT SYSDATE, ROUND(SYSDATE, ‘Wed'),

    TRUNC(SYSDATE, ‘Wed')

    FROM u.agg_ia_clickstream

    SYSDATE ROUND(SYSDA TRUNC(SYDATE

    -------------- ------------------------------------------------

    31-Dec-2003 01-Jan-2004 01-Dec-2003

    Rounding allows the date to be rounded up to the nearest month. Truncation allows the date of any value you specify to its minimum. The minimum value is 1, so 31-Dec was truncated to 1-Dec.

  2. The REPLACE function can be used to do a search and replace:

    REPLACE(string, search, replace)

  3. Extract data from a string using:

    SUBS(STRING, START[, length])

  4. Find the length of a string:

    LENGTH(string)

  5. Add strings together:

    String1 ll String2

  6. Trim unwanted spaces, numbers, and letters:

    Trim(string)

    Trim(character FROM string)

    To trim only characters to the right:

    RTRIM(string)

    To trim characters only to the left:

    LTRIM(string)

    To remove punctuation from both sides of a string:

    RTRIM(LTRIM(string,'.;!'),'.,!')

  7. Change capitalization of letters:

    To Change letters to upper case letters:

    UPPER(string)

    To change letters to lower case letters:

    LOWER(string)

    To use upper case for the first letter and lower case for other letters:

    INITCAP(string)

  8. Search and Replace:

    SELECT date, unique cookies

    DECODE (dma, 360, ‘Dayton', 560, ‘Toledo', 870, ‘Baton Rouge', Null, ‘Unknown')

    FROM u.agg_ia_clickstream

    In this example, 360 is replaced by Dayton, 560 is replaced by Toledo, etc.

The 8 formatting tips above will help you make your data -- and hopefully you, by association -- look good. If you know other tips that are helpful, please let me know. Thank you for reading.

 

Share this article!

Follow us!

Find more helpful articles: