Category: Oracle Development

PL/SQL data formatting

I am trying to develop a PL/SQL function to perform some data cleansing. One of the data cleansing requirements is If there are instances of more than 1 space together, the extra spaces should be removed.

Say for example, if my data comes in as "123 456 789", then my function should clean it as "123 456 789" replacing multiple spaces with just 1 space.

What PL/SQL functions could I use to accomplish this?

Thanks

Krish

Hello!

I am trying to develop a PL/SQL function to perform some data cleansing. One of the data cleansing requirements is If there are instances of more than 1 space together, the extra spaces should be removed.

Say for example, if my data comes in as "123 456 789", then my function should clean it as "123 456 789" replacing multiple spaces with just 1 space.

What PL/SQL functions could I use to accomplish this?

Thanks

Krish

replace, loop replacing double space with single untill no more replaces are made.

Krish

Krish

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(:value_with_spaces)), ' ', ' '),' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')

(the trims aren't necessary but remove any leading or trailing spaces)

This deals with every number of spaces I've tried it with, but as I said I'm no mathematician.

Andrew

The numbers in the description are how many spaces should be in each one.

Krish

Hi i am also facing same problem means i need to remove extra space with one space. Can you please share any sample code with me