Home » SQL & PL/SQL » SQL & PL/SQL » remove spaces by excluding double quotes from a string in sql (10.2.0.4.0)
remove spaces by excluding double quotes from a string in sql [message #569529] Mon, 29 October 2012 05:09 Go to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I want to remove more than one space from a string by excluding double quotes.

Please advice.

For example:

I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")

O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")
Re: remove spaces by excluding double quotes from a string in sql [message #569532 is a reply to message #569529] Mon, 29 October 2012 05:31 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member


select REPLACE ('Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement "',' ') from dual 
Re: remove spaces by excluding double quotes from a string in sql [message #569536 is a reply to message #569532] Mon, 29 October 2012 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is silly answer (once more).
Did you actually read the question and the wanted result?

Regards
Michel
Re: remove spaces by excluding double quotes from a string in sql [message #569537 is a reply to message #569536] Mon, 29 October 2012 05:51 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Sorry try..
it will remove more than 2 spacess
select regexp_replace( 'murali.kri','( ){2,}',' ' )  from dual 
Re: remove spaces by excluding double quotes from a string in sql [message #569539 is a reply to message #569537] Mon, 29 October 2012 06:06 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
I know below query removes more than one space in a string

select regexp_replace( 'murali.kri','( ){2,}',' ' ) from dual

but I dont want that. In my string , I have double quotes with string (includes spaces) and I dont want to remove those spaces which are having within double quotes.

For example:

I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")

O/P: Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")

Please see the above o/p for better understanding.
Re: remove spaces by excluding double quotes from a string in sql [message #569542 is a reply to message #569537] Mon, 29 October 2012 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
muralikri wrote on Mon, 29 October 2012 11:51
Sorry try..
it will remove more than 2 spacess
select regexp_replace( 'murali.kri','( ){2,}',' ' )  from dual 


You still did not CAREFULLY read the question and result.

Regards
Michel
Re: remove spaces by excluding double quotes from a string in sql [message #569544 is a reply to message #569542] Mon, 29 October 2012 06:25 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
To remove only spaces, that are not between double quotes, you could
1. identifiy these spaces (between double quotes)
2. replace them with a letter, that doesn't exist in your strings (here '#')
3. remove the other spaces
4. replace back the '#' with spaces
SELECT 
  REPLACE (
    REPLACE (
      REGEXP_REPLACE ('Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")',
       '"(\w*)( )(\w*)"','\1#\3'), ' ', ''), '#', ' ') sstr
  FROM dual;
sstr
-------------------------------------------------
Item.getChildByType( Agreement).getParent().hasChildByType(Agreement )

Hope that someone finds a smarter soultion Wink

[Updated on: Mon, 29 October 2012 06:27]

Report message to a moderator

Re: remove spaces by excluding double quotes from a string in sql [message #569546 is a reply to message #569544] Mon, 29 October 2012 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is one is smarter enough. Wink

Regards
Michel
Re: remove spaces by excluding double quotes from a string in sql [message #569549 is a reply to message #569546] Mon, 29 October 2012 06:44 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you for smarter reply but I want to keep my double quotes as it is.
Re: remove spaces by excluding double quotes from a string in sql [message #569550 is a reply to message #569549] Mon, 29 October 2012 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is just a small bug in the expression, _jum forgot to put the " in the replacing string, I didn't even notice it:
SQL> SELECT 
  2    REPLACE (
  3      REPLACE (
  4        REGEXP_REPLACE ('Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")',
  5         '"(\w*)( )(\w*)"','"\1#\3"'), ' ', ''), '#', ' ') sstr
  6    FROM dual;
SSTR
--------------------------------------------------------------------------
Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")

Regards
Michel

[Updated on: Mon, 29 October 2012 06:51]

Report message to a moderator

Re: remove spaces by excluding double quotes from a string in sql [message #569551 is a reply to message #569550] Mon, 29 October 2012 07:48 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much for quick reply.

This is working if single space with one word within double quotes.This is not working if more than once space with more than one word within double quotes.It should not remove any space within the double quotes and if I have space at the end of the string also should remove.Please advice.

For example:

i/p: Item .getChildByType (" Sub Agreement").getParent( )
o/p: Item.getChildByType(" Sub Agreement").getParent()
Re: remove spaces by excluding double quotes from a string in sql [message #569552 is a reply to message #569529] Mon, 29 October 2012 07:56 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
You can try this.
select 'x' || regexp_replace(src, 
       '([^" ]*)( *)([^" ]*)' || --Unquoted value with space
       '("[^"]*")*' ||           --Quoted value
       '([^" ]*)( *)*([^" ]*)'    --Unquoted value with space  
       ,'\1\3\4\5\7')
       || 'x'
from (
     select 'Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ") some argument ("  ") here(" yeah ? ") ' src 
     from dual)





When I run this I get:
xItem.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")someargument("  ")here(" yeah ? ")x


Re: remove spaces by excluding double quotes from a string in sql [message #569553 is a reply to message #569552] Mon, 29 October 2012 08:14 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much for all and it is working for all the strings.
Re: remove spaces by excluding double quotes from a string in sql [message #569554 is a reply to message #569553] Mon, 29 October 2012 08:27 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
I was being a little bit silly.. This should be enough: Smile

select 'x' || regexp_replace(src, 
       '([^" ]*)( *)([^" ]*)' || --Unquoted value with space
       '("[^"]*")*'              --Quoted value
       ,'\1\3\4')
       || 'x'
from (
     select 'Item .getChildByType("   Agreement").getParent( ) .hasChildByType("Agreement ") some argument ("  ") here(" yeah ? ") ' src 
     from dual)


Result:
xItem.getChildByType("   Agreement").getParent().hasChildByType("Agreement ")someargument("  ")here(" yeah ? ")x
icon14.gif  Re: remove spaces by excluding double quotes from a string in sql [message #569559 is a reply to message #569554] Mon, 29 October 2012 09:13 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Remarkable SMART!

[Updated on: Mon, 29 October 2012 09:13]

Report message to a moderator

Re: remove spaces by excluding double quotes from a string in sql [message #569581 is a reply to message #569559] Mon, 29 October 2012 23:56 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Can you please explain what indicates '\1\3\4'?
Re: remove spaces by excluding double quotes from a string in sql [message #569585 is a reply to message #569581] Tue, 30 October 2012 01:09 Go to previous messageGo to next message
Littlefoot
Messages: 19885
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Backreferences, I believe. The first sub-expression is referenced with \1, the second one would be \2, the third one is \3, etc.
Re: remove spaces by excluding double quotes from a string in sql [message #569602 is a reply to message #569585] Tue, 30 October 2012 04:18 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Yes as Littlefoot says it's a backreference. And as you see the second paranthesis contains the expression with the space. So by not including number 2 in the returned value, the spaces are removed.
Previous Topic: why did not "no_merge" work?
Next Topic: Difference between Deterministic functions and Function result cache in 11g
Goto Forum:
  


Current Time: Thu Dec 18 17:56:14 CST 2014

Total time taken to generate the page: 0.09070 seconds