-
Hi gang,
Does anyone know of any oracle function that I can use to delete extraneous spaces between words in a string. For example, the input string 'this is a test data end here'.
Rtrim and Ltrim will not delete between words!
Thanks,
Leonard905
leonard905
leonard905@yahoo.com
-
Even I, if you give me that piece of text I wouldnt know which space do I have to delete?
'this is a test data end here'
which space has to be deleted?
-
I don't think it can be done simply by combining built-in SQL functions. The easiest way is to built your own PL/SQL function taht will remove redundat spaces. The following one is replacing two consecutive spaces with a single space until there is no more "double spaces" in the string.
[code]
create or replace function remove_redundant_spaces (p_instr in varchar2)
return varchar2 is
v_outstr varchar2(4000) := p_instr;
v_length_before number := 0;
v_length_after number := length(v_outstr);
begin
while v_length_before != v_length_after loop
v_length_before := v_length_after;
v_outstr := REPLACE(v_outstr, ' ', ' '); -- replace two blanks with single blank
v_length_after := length(v_outstr);
end loop;
return v_outstr;
end;
/
[code]
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by pando
Even I, if you give me that piece of text I wouldnt know which space do I have to delete?
'this is a test data end here'
which space has to be deleted?
Pando, you have to look at the HTML source (or click "quote" on the original post) to see there were many spaces between individual words.....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
if it was to replace two blanks you could use replace()
Code:
select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
las lala
select replace(dname, ' ',' ') "removing extra space" from dept:
removing extra
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
las lala
thanx jmodic I didnt know I had to read in html
-
The entry was automatically reset by the browser, maybe. Here it is again:
'this is a data test end here'
Thanks,
Leonard
leonard905
leonard905@yahoo.com
-
Originally posted by pando
if it was to replace two blanks you could use replace()
select replace(dname, ' ',' ') "removing extra space" from dept:
This only works if there are exactly 2 blanks between the two words (like in your case between 'las' and 'lala'). What if there are 3 consecutive blanks between two words? Or 15? or 37? .... You have to do it in a loop.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
I don't think it can be done simply by combining built-in SQL functions
And here I was waiting for this elaborate SQL statement using DECODE, CASE, REPLACE, etc...
Jeff Hunter
-
YEa I did so because I readextra space not extra spaceS
-
Can I request more help here: Below is my table from which the data comes:
SVRMGR> desc test
Column Name Null? Type
------------------------------ -------- ----
REMARKS VARCHAR2(500)
SVRMGR>
How I can I customize the function (previous thread) to be used on this table?
Thanks,
Leonard905
leonard905
leonard905@yahoo.com
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
|