Home » SQL & PL/SQL » SQL & PL/SQL » replace space with *
replace space with * [message #380403] Sun, 11 January 2009 20:02 Go to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
Hi,
this is my query:
select replace((orig_system_reference||' '),'*'),orig_system_reference
from ar.hz_cust_accounts
where orig_system_reference= '&orig_system_reference';

orig_system_reference=048893
after 048893 had one space
i wnat to replce that space with * so i used replce function but its not working so please help me.
Re: replace space with * [message #380406 is a reply to message #380403] Sun, 11 January 2009 20:25 Go to previous messageGo to next message
goodhawk
Messages: 3
Registered: September 2006
Junior Member
SELECT replace(a.v,CHR(32),CHR(42)),ascii(' '),ASCII('*') FROM zzhawk a;
Re: replace space with * [message #380407 is a reply to message #380403] Sun, 11 January 2009 20:34 Go to previous messageGo to next message
goodhawk
Messages: 3
Registered: September 2006
Junior Member
SELECT REPLACE (a.v, CHR (32), CHR (42)), ASCII (' '), ASCII ('*')
FROM zzhawk a;
Re: replace space with * [message #380413 is a reply to message #380403] Sun, 11 January 2009 21:41 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Please do not multi-post and read the Forum Guidelines on how to format your posts.

Your query was trying to replace a '*' and not a space.

SQL> with t as (
  2      select '048893 ' num from dual)
  3  select num, length(num), replace(num, ' ', '*')
  4  from t;

NUM     LENGTH(NUM) REPLACE
------- ----------- -------
048893            7 048893*
Re: replace space with * [message #380474 is a reply to message #380403] Mon, 12 January 2009 00:49 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi,

If the length of "orig_system_reference" column is fixed for 6 digits,
following would be better.

Raja>create table test_table
  2  (test_col varchar2(6));

Table created.

Raja>insert into test_table values('048893');

1 row created.

Raja>select test_col, rpad(test_col,7,'*')
  2  from test_table
  3  where test_col = '&test_col';
Enter value for test_col: 048893
old   3: where test_col = '&test_col'
new   3: where test_col = '048893'

TEST_C RPAD(TE
------ -------
048893 048893*

1 row selected.


regards,
Delna
Re: replace space with * [message #380549 is a reply to message #380474] Mon, 12 January 2009 05:11 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
delna.sexy wrote on Mon, 12 January 2009 01:49

If the length of "orig_system_reference" column is fixed for 6 digits, following would be better.


Why impose additional conditions on the data when the OP asked for:

radhavijaym wrote on Sun, 11 January 2009 21:02
i wnat to replce that space with *

Re: replace space with * [message #380647 is a reply to message #380549] Mon, 12 January 2009 19:06 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:

Why impose additional conditions on the data when the OP asked for:



It was just suggestion.

It is possible that OP is missing something, or may be he/she has not that thing in its mind.
There may be some other possible ways to do the same thing with better performance. And that is why I suggest to use RPAD().

regards,
Delna
Previous Topic: SQL 1999 problem
Next Topic: UTL_FILE vs External Tables (merged by bb)
Goto Forum:
  


Current Time: Sat Dec 10 01:12:24 CST 2016

Total time taken to generate the page: 0.05297 seconds