-
Hi,
How do I remove blanhs from with a string using a select statement.
For example the tring is 'Ron nie' and I want my select to get 'Ronnie'.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
SELECT REPLACE('Ron nie', ' ') FROM dual;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks,
Actually I am looking for something where the whitespace is removed only after a comma and not otherwise
Like in this case the blank should not be removed in the statement
'Tom Hanks'
But in this case the blank should be removed
'Tom, Hanks'
Notice the comma here. Hence I am trying to remove blank spaces around a comma and in all other cases it should ignore
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Code:
SQL> SELECT REPLACE('Tom Hanks' , ', ', ',') FROM dual;
REPLACE('
---------
Tom Hanks
SQL> SELECT REPLACE('Tom, Hanks' , ', ', ',') FROM dual;
REPLACE('
---------
Tom,Hanks
SQL>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks Jurij,
I want to remove all the occurences of whitespaces after a comma.
This is what i get with the above query
1* SELECT REPLACE('Tom, Hanks' , ',  ', ',') FROM dual
SQL> /
REPLACE('TOM,    HANKS',',',',')
---------------------------------
Tom,    Hanks
It should be
Tom,Hanks
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
I'm affraid you'll have to use PL/SQL for that. Something like the following function:
Code:
SQL> create or replace function remove_blanks_after_comma (p_in in varchar2)
2 return varchar2 as
3 v_out varchar2(4000) := p_in;
4 begin
5 while INSTR(v_out, ', ') > 0 loop
6 v_out := replace(v_out, ', ', ',');
7 end loop;
8 return v_out;
9 end;
10 /
Function created.
SQL> SELECT remove_blanks_after_comma('Tom, Hanks') FROM dual;
REMOVE_BLANKS_AFTER_COMMA('TOM,HANKS')
--------------------------------------------------------------------------------
Tom,Hanks
SQL> SELECT remove_blanks_after_comma('Tom Hanks') FROM dual;
REMOVE_BLANKS_AFTER_COMMA('TOMHANKS')
--------------------------------------------------------------------------------
Tom Hanks
SQL>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|