Function to leave only one space between words

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
arthae
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Mon, 09 May 2011 1:13 pm
Location: Porto Alegre - RS
GIVE ME COFFEE AND NO ONE GETS HURT!

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
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Mon, 09 May 2011 1:13 pm
Location: Porto Alegre - RS
GIVE ME COFFEE AND NO ONE GETS HURT!

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
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Mon, 09 May 2011 1:13 pm
Location: Porto Alegre - RS
GIVE ME COFFEE AND NO ONE GETS HURT!

arthae wrote: 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
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 111
Joined: Thu, 22 Feb 2007 2:34 pm
Location: DF
________________
http://lordelfo.blogspot.com
Thiago Façanha

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
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE

Oops,
I believe I would get better using RegEXP.

Select all

SELECT REGEXP_REPLACE(TRANSLATE('Palavras     2349 juntas      .',' 1234567890',' '), '( ){2,}', ' ') 
FROM DUAL;
arthae
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Mon, 09 May 2011 1:13 pm
Location: Porto Alegre - RS
GIVE ME COFFEE AND NO ONE GETS HURT!

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
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE

Dude, I'm using 10 here and it works beauty.
What is your version ????
arthae
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Mon, 09 May 2011 1:13 pm
Location: Porto Alegre - RS
GIVE ME COFFEE AND NO ONE GETS HURT!

Noctifero wrote: 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
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE

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
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Mon, 09 May 2011 1:13 pm
Location: Porto Alegre - RS
GIVE ME COFFEE AND NO ONE GETS HURT!

Noctifero wrote: 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
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE

Being a trainee is fuck ...
may or see Blogs ...
Heheheheheheh.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest