How to Remove Space From a Data Oracle SQL

Save

There are several different methods for removing spaces from data retrieved in an Oracle results set. Oracle PL/SQL provides several different character functions for removing spaces, and which one you will need to use will depend on the data and where the spaces occur in the retrieved data.

  • You can use the CHR (Character) function to remove spaces from mailing addresses.
    Suppose that you want to retrieve customer data from the database, but in some circumstances missing address information will cause additional blank lines in the address:
    SELECT custname, address1, address2, address3, address4, custcity || ', ' || custstate || " " || zipcode
    from businessname;

    Thomas Smith
    10 Main Street
    Suite 201

    New York, NY 02166

    where there are spaces between the suite and city. To remove these spaces, you can use the CHR function to suppress the internal blank lines:

    SELECT custname ||

                DECODE (address1,  NULL,  NULL,  CHR (10) || address1) ||
    DECODE (address2, NULL, NULL, CHR (10) || address2) ||
    DECODE (address3, NULL, NULL, CHR (10) || address3) ||
    DECODE (address4, NULL, NULL, CHR (10) || address4) ||
    CHR (10) ||
    custcity || ', ' || custstate || ' ' || zipcode

    FROM businessname,

    Thomas Smith
    10 Main Street
    Suite 201
    New York, NY 02166

    Blank address lines are ignored and the address prints properly. You may have to add the wrap function in the reports field that holds this string.

  • You can use the left trim function (LTRIM) to remove leading spaces. LTRIM removes characters from the leading positions in a string.
    LTRIM ( ' bbbb This is an example of removing leading spaces') results in
    'This is an example of removing leading spaces'. All of the leading spaces have been removed from the data.

  • You can use the right trim (RTRIM) function to remove spaces at the end of a string.

    RTRIM deletes characters from the end of a string.
    RTRIM ( ' This is an example of removing ending spaces bbbb') results in
    'This is an example of removing ending spaces'. All of the ending spaces have been removed from the data.

Tips & Warnings

  • There are other character functions available in Oracle PL/SQL, such as replace, that could also be used in certain circumstances.

References

Promoted By Zergnet

Comments

You May Also Like

  • How to Replace Tabs From Oracle Output

    Horizontal tab characters can cause formatting errors in Oracle output because computer operating systems do not always look at tabs the same....

  • How to Remove Leading Zeros in SQL

    SQL allows the programmer to store data from any form of document, even images and rich media, directly within the server. The...

  • How to Use Control Characters With Oracle SQL

    A control character is a carat-shaped character located on the keyboard. Control characters indicate carriage return or tab or backspace. Type a...

  • How to Remove Blank Spaces in SQL

    In the programming world, it's important that data entered by users be validated. Users commonly enter data through text boxes, list boxes...

  • How to Trim in T-SQL

    T-SQL (Transact SQL) is the proprietary language used by Microsoft SQL Server. It is an extension of the SQL (Structured Query Language)...

  • String Functions in Oracle SQL

    String functions in an Oracle database system can perform many different tasks, and developers often make use of SQL string functions regardless...

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!