removing white spaces

[williey]

How do you remove white spaces of a field in stored procedure prior to inserting the record?

Does PL/SQL provide an easy way to do this?

 

[SantaMufasa]

Willie,

I'm certain we can resolve your need. Could you please post a brief/simple example of the "white space" you would like to remove and how you would like the white-space-removed results to look?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[williey]

Oops!..

An example of the white spaces I'm refering to is such "DEL SOLAR"

It white spaces in between the text. The need to create email accounts from names and there are last name which consists of 2 words.

Thanks!

I can do this in Perl but it will be more efficient to handle everything in PL/SQL instead.

 

[SantaMufasa]

Willie,

What it looks like you want is a function (let's call it "NO_WHITE" after our favourite Disney character) that results in a string with all white space squeezed out. White space, for the sake of this discussion, is any number of blanks, tabs [chr(9)], carriage returns [chr(13)], and line feeds [chr(10)]. You can certainly add more to the list of "known white space codes" if you wish:

Section 1 -- "NO_WHITE" function code:

create or replace function no_white (str_in varchar2) return varchar2 is
    known_white_space_codes varchar2(50)
        := ' '    -- blank space
        ||chr(9)  -- tab
        ||chr(13) -- carriage return
        ||chr(10) -- line feed
        ;
begin
    return translate(str_in,'^'||known_white_space_codes,'^');
end;
/

Section 2 -- Sample invocation of "NO_WHITE":

col a heading "Squeezed|Result" format a10
select no_white('Dave '||chr(9)||chr(13)||chr(10)||' Hunt ') a from dual;

Squeezed
Result
--------
DaveHunt

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[amrita418]

I may be confused with your requiment but if you just need to remove spaces in a column you can try using translate or the replace functions.


Regards,
AA

 

[SantaMufasa]

No, Amrita, I'm sure you understand the requirement just fine. It is just syntactically simpler to say:

...no_white(column_name)...

than

...translate(column_name,'^'||chr(9)||chr(13)||chr(10),'^')...

...don't you agree?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[amrita418]

I agree using translate would be slightly cumbersome but replace should do the trick.

select replace('Test Will iey', ' ', '') || '@testmail.com' from dual;

Regards,
AA

 

[lewisp]

amrita, I suggest you look a little more closely at Daves solution. Although Williey's original requirement suggest that only single spaces are to be removed, Daves excellent solution (using translate) covers for any eventuality of white space, including tabs and carriage returns.

 

[amrita418]

My apologies if I gave an impression that I am not in agreement with Dave's code. All I was saying was if the field contains only space characters then replace function should suffice.

Regards,
AA