Home » SQL & PL/SQL » SQL & PL/SQL » Is there a function to repeat a character ? (10g2)
Is there a function to repeat a character ? [message #316742] Mon, 28 April 2008 03:11 Go to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Is there a function that repeats a character a given number of times and returns this as a character string ?
For instance to check if a VARCHAR2(30) field contains only spaces :
if table.column = some_build_in_function(30, ' ')
...
end if;

is the same as ...
if table.column = '                              '
...
end if;


thanks,

Walter
Re: Is there a function to repeat a character ? [message #316743 is a reply to message #316742] Mon, 28 April 2008 03:21 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Look at the LPAD or RPAD functions
Re: Is there a function to repeat a character ? [message #316746 is a reply to message #316742] Mon, 28 April 2008 03:35 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks pablolee,

It's not what I had in my mind but something like
lpad(' ', 30, ' '),
will work just fine.

regards,

Walter
Re: Is there a function to repeat a character ? [message #316771 is a reply to message #316743] Mon, 28 April 2008 05:06 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

even if you use lpad, rpad for '-', ' ' the multiple characters
are getting shrinked and displays as single character only in apex pages.

For example 'raghu--------' will display as 'raghu-'. This doubt has been raised in Applection express MOD still it is unanswered..

yours
dr.s.raghunathan
Re: Is there a function to repeat a character ? [message #316774 is a reply to message #316771] Mon, 28 April 2008 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What APEX has to do here?
Or are you just enumerating bugs you know?

Regards
Michel
Re: Is there a function to repeat a character ? [message #316779 is a reply to message #316774] Mon, 28 April 2008 05:26 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
yes

i am facing the problem similar to this when using with apex..
that's why i am trying to different focus point to my problem too..
yours
dr.s.raghunathan]

Re: Is there a function to repeat a character ? [message #316781 is a reply to message #316779] Mon, 28 April 2008 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then? There are many bugs everywhere in Oracle, will you enumerate all the bugs you know on each question?

Regads
Michel
Re: Is there a function to repeat a character ? [message #316784 is a reply to message #316781] Mon, 28 April 2008 05:39 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
no

this is very pertinent to me...

is there any harm on doing so when i desparately need of solution?

yours
dr.s.raghunathan
Re: Is there a function to repeat a character ? [message #316786 is a reply to message #316784] Mon, 28 April 2008 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! this was a question not an information.
You should post it in APEX and not hijack another topic above all in the wrong forum, this will prevent from misunderstanding.

Regards
Michel
Re: Is there a function to repeat a character ? [message #316787 is a reply to message #316786] Mon, 28 April 2008 05:49 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
ok..
i am tired.. and closing the debate..



yours
dr.s.raghunathan
Re: Is there a function to repeat a character ? [message #316826 is a reply to message #316787] Mon, 28 April 2008 08:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just a thought, since we're on the Apex-path anyway.
Are you sure the text has a single space, or does it just display in your browser as a single space?
Maybe you should use   instead of ' '.
Re: Is there a function to repeat a character ? [message #316870 is a reply to message #316826] Mon, 28 April 2008 11:48 Go to previous messageGo to next message
rayessir
Messages: 5
Registered: April 2008
Junior Member
hi
Quote:


use &nbsp instead of ' '




it is fine with apex report...

when it is used with BIP it displays &nbsp

Through sql command window, it displays as &nbsp itself...

Re: Is there a function to repeat a character ? [message #317198 is a reply to message #316742] Tue, 29 April 2008 21:32 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Walter, lemme see if i interpret your first message correctly as i just had to do some painstaking string work on a project (like take string1 and string2 and see if any n amount of chars is in string2, or concatonate string1,2,3,4 - stip double spaces to single then take the first letter after each space and build an acronym to check against another field -
a lot of fuzzy name comparissons on insurance claims.

Anyway if you wanted a generic function to accept a string and see if it is all spaces (or any character for that matter)

you could have something like (im typing this off the top of my head, pls excuse fat fingering on syntax!)

create or replace function isSpaces(text1 in varchar2 return number)
deterministic -- always a performance help when true
IS
lvText varchar2(255) := null;
BEGIN
lvText := text1;

while instr(lvText,' ') > 0 loop
replace lvText(' ',' ');
end loop;

if lvtext := ' ' then
return 1; -- 'yes'
end if;

return 0; -- 'no'

end isSpaces;

then anywhere you can do stuff like

select count(*) as space_rec_count from table_A where
isSpaces(table_A.some_string_field) > 0;

the function isSpaces could be turned generic if you pass in
the character(S) you are checking for and just reduce via replace.


not sure if thats what you were really gaging towards but
thought i'd share because i have found over time that building one's own little utility collection of generic string functions
in the long run can remove having to retype long
instr() substr() nested funcs to form algorithms of similar nature.

best wishes
harry
Re: Is there a function to repeat a character ? [message #317209 is a reply to message #317198] Wed, 30 April 2008 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Harry,

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Is there a function to repeat a character ? [message #317211 is a reply to message #317209] Wed, 30 April 2008 01:01 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
sorry about that - will do, Mike - thanks for the heads-up,
Harry
Re: Is there a function to repeat a character ? [message #317292 is a reply to message #316742] Wed, 30 April 2008 06:43 Go to previous message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks Harry,

Your function is not exactly what I was looking for but I foresee that I will probably require something like this as my project progresses.


Walter
Previous Topic: spooling error
Next Topic: How to generate XLS from SQL*Plus?
Goto Forum:
  


Current Time: Tue Dec 06 10:03:07 CST 2016

Total time taken to generate the page: 0.09625 seconds