Home » SQL & PL/SQL » SQL & PL/SQL » how to reove spaces and it becomes standard
how to reove spaces and it becomes standard [message #253392] Mon, 23 July 2007 14:11 Go to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

hi everyone,

i am giving to queries , actually first contains one space between one space and second contain two spaces .....

now i want if the values are same then i forms 6i should alert the user that the value is already exist...... if the words in a value contain 1 space or more than 1.....(i am taking about in between the values in a text item , not at the beginning or at the ending of a value, becoz it is simply solved by using ltrim or rtrim functions

queries are :

select * from emp where ename = 'RAJAT CHAUDHARY';

select * from emp where ename = 'RAJAT  CHAUDAHARY';

so the result should come same after using only 1 space between values.....


REGARDS
RAJAT CHAUDHARY
Re: how to reove spaces and it becomes standard [message #253393 is a reply to message #253392] Mon, 23 July 2007 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom thread Replacing Multiple Blanks by single blanks in text

Regards
Michel
Re: how to reove spaces and it becomes standard [message #254088 is a reply to message #253393] Wed, 25 July 2007 13:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you are in 10g :

The following example examines the string, looking for two or more spaces. 
Oracle replaces each occurrence of two or more spaces with a single space.

SELECT
  REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',
                 '( ){2,}', ' ') "REGEXP_REPLACE"
  FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA



Courtesy : Oracle Manual sql reference

[Updated on: Wed, 25 July 2007 13:45] by Moderator

Report message to a moderator

Re: how to reove spaces and it becomes standard [message #254108 is a reply to message #254088] Wed, 25 July 2007 14:41 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

RAJARAM

actually i am working on oracle 9.0 release
and
i want to implement that functionality in frontend 6i
Re: how to reove spaces and it becomes standard [message #254109 is a reply to message #254108] Wed, 25 July 2007 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually you have to read the link I posted.

Regards
Michel
Re: how to reove spaces and it becomes standard [message #254111 is a reply to message #254109] Wed, 25 July 2007 14:47 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

thanks micheal,

still i have to go through that
by the way thanks again
Re: how to reove spaces and it becomes standard [message #254113 is a reply to message #254111] Wed, 25 July 2007 14:51 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it will be useful as this question has been answered by many people in so many different ways.

Regards
Michel
Previous Topic: Sql Help, please! - could I have come up with a less helpful title?
Next Topic: opening n ending balance
Goto Forum:
  


Current Time: Thu Dec 08 10:07:57 CST 2016

Total time taken to generate the page: 0.15568 seconds