Function to leave only one space between words

[arthae]

Hey guys.
I am here making a function to take numbers of the phrase, but when I run the function it leaves a space for each number that has been withdrawn. If I give a Replace he takes all the spaces and let the words together!
The intention is to leave only 1 space between words. Follows the example:

Select all

 
SELECT TRIM(TRANSLATE( 'Palavras 2349 juntas ', 
                                        '1234567890', 
                                        '          ')) 
FROM DUAL

Result: 'Words together'

with Replace

Select all

 
SELECT REPLACE(TRIM(TRANSLATE( 'Palavras 2349 juntas' , 
                               '1234567890', 
                               '          ')),' ', NULL) 
FROM DUAL 

Result: ' Wanders'


Any tip or suggestion for a trainee in the Oracle world?

[arthae]

I was testing and I was able to do this

Select all

 
SELECT REPLACE(REPLACE(TRIM(TRANSLATE( TRANSLATE('PALAVRAS 2349 JUNTAS',' ','_'), 
                               '1234567890', 
                               '          ')),' ', NULL),'__',' ') 
FROM DUAL 

is already something! Who has more ideas please post!
Thanks!

[arthae]

was testing and I was able to do this

Select all

 
SELECT REPLACE(REPLACE(TRIM(TRANSLATE( TRANSLATE('PALAVRAS 2349 JUNTAS',' ','_'), 
                               '1234567890', 
                               '          ')),' ', NULL),'__',' ') 
FROM DUAL 

is already something! Who has more ideas please post!
Thanks!

Select all

upDATE 
 
SELECT REPLACE( 
       REPLACE( 
       REPLACE(TRIM(TRANSLATE(  
                    TRANSLATE('PALAVRAS2349 JUNTAS',' ','_'), 
                    '1234567890', 
                    '          ')) 
       ,' ', NULL) 
       ,'__',' ') 
       ,'_',' ') 
FROM DUAL

[LordElfo]

Opa face beleza?
Type I did not understand your difficulty ...

Your translate code is replacing by '' -> various spaces ...
But what do you Do you want it just a correct one ???


So why do not you use it?

Select all

SELECT TRIM(TRANSLATE( 'Palavras 2349 juntas ', 
                                        '1234567890', 
                                        ' ')) 
FROM DUAL 

Would this be?

[DanielNN]

Oops,
I believe I would get better using RegEXP.

Select all

SELECT REGEXP_REPLACE(TRANSLATE('Palavras     2349 juntas      .',' 1234567890',' '), '( ){2,}', ' ') 
FROM DUAL;

[arthae]

Thanks for the answers!
@ lordelfo
If I do this way he still continues with 2 spaces between words, the idea is to have only 1!
But it already helped a lot! Thanks!


@ Noctifero
This regexp function should have any version of the bank? Gave as invalid indentifier.

[DanielNN]

Dude, I'm using 10 here and it works beauty.
What is your version ????

[arthae]

Dude, I'm using 10 here and it works beauty.
What is your version ????

Here is 9, I think it's just for version 10 above!

[DanielNN]

I saw here that the bank version 9 does not have this.
But I found here that some charitable soul has made available its alternative implementation of these functions of regular expressions: http://phil-sqltips.blogspot.com/2009/0 ... le-9i.html

[arthae]

I saw here that the bank version 9 does not have this.
But I found here that some charitable soul has made its alternative implementation of these functions of regular expressions: http://phil-sqltips.blogspot.com/2009/0 ... le-9i.html / quote]

Thanks Noctifero, but here in the company the site is Blocked by being a blog haha ??...
and for trainee they do not release the sites so in the largest. But I was able to take a good base already (:
Thanks people!

[DanielNN]

Being a trainee is fuck ...
may or see Blogs ...
Heheheheheheh.