Home » SQL & PL/SQL » SQL & PL/SQL » Can this be done with a REGEXP (10.2)
Can this be done with a REGEXP [message #428575] Wed, 28 October 2009 23:43 Go to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I want to do a REGEXP_REPLACE style function. Give a string with embedded linefeeds (ASCII 10) I want to replace any space characters immediately following the linefeed with "&nbsp".

For example, by applying the function over the following:
select 'This line has none
    this line has four
 this line has one'
from dual
/

You would get
This line has none
&nbsp&nbsp&nbsp&nbspthis line has four
&nbspthis line has one


Short of writing my own function, I can't work out how to do this unless the number of spaces in the indentation is known.

The idea is - if you haven't worked it out already - that I want to present a free format string in HTML with manual spaced indentation preserved, but I don't want to use <PRE> tags, which would disable line wrapping.

Ross Leishman
Re: Can this be done with a REGEXP [message #428576 is a reply to message #428575] Wed, 28 October 2009 23:52 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
  1  select regexp_replace('This line has none
  2      this line has four
  3   this line has one','( ){1}','_')
  4* from dual
SQL> /

REGEXP_REPLACE('THISLINEHASNONETHISLINEHASFOURTHISLINEHASONE
------------------------------------------------------------
This_line_has_none
____this_line_has_four
_this_line_has_one


Re: Can this be done with a REGEXP [message #428577 is a reply to message #428576] Wed, 28 October 2009 23:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes, I had gotton that far myself. But I only want the LEADING spaces replaced. Not EVERY space.

Ross Leishman
Re: Can this be done with a REGEXP [message #428580 is a reply to message #428577] Thu, 29 October 2009 00:51 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> select REGEXP_REPLACE(regexp_replace('This line has none
  2      this line has four
  3   this line has one','( ){1}','_'),
  4  '([ [ :alpha: ] ])_([ [ :alpha: ] ])',
  5  '\1 \2') RESULT
  6  from dual
  7  /

RESULT
------------------------------------------------------------
This line has none
____this line has four
_this line has one




How about this Wink
I think this can be written using one regexp as well

[Updated on: Thu, 29 October 2009 00:55]

Report message to a moderator

Re: Can this be done with a REGEXP [message #428593 is a reply to message #428580] Thu, 29 October 2009 01:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Getting closer, but it only works for alpha (I can tweak that, though) and it only works when non-leading spaces appear singly.

  1  select REGEXP_REPLACE(regexp_replace('This line has none
  2      this   line has four
  3   this line has one','( ){1}','_'),
  4  '([url=http://www.orafaq.com/wiki/:alpha:]:alpha:[/url])_([url=http://www.orafaq.com/wiki/:alpha:]:alpha:[/url])',
  5  '\\1 \\2') RESULT
  6* from dual
SQL> /

RESULT
--------------------------------------------------------------
This line has none
____this___line has four
_this line has one


Any more ideas?

Ross Leishman
Re: Can this be done with a REGEXP [message #428595 is a reply to message #428593] Thu, 29 October 2009 01:53 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> ed
Wrote file afiedt.buf

  1   select REGEXP_REPLACE(regexp_replace('This line has none
  2       this line has      four
  3    this line has one','( ){1}','_'),
  4   '([ [ :alpha: ] ])_{1,}([ [ :alpha: ] ])',
  5   '\1 \2') RESULT
  6*  from dual
SQL> /

RESULT
--------------------------------------------------------------
This line has none
_____this line has four
__this line has one


but it trims it to one space..is it ok??
as in html spaces hardly matter

[Updated on: Thu, 29 October 2009 01:53]

Report message to a moderator

Re: Can this be done with a REGEXP [message #428600 is a reply to message #428595] Thu, 29 October 2009 02:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. I might be able to work with that. Thanks.
Re: Can this be done with a REGEXP [message #428616 is a reply to message #428595] Thu, 29 October 2009 02:48 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
what about something like:
select regexp_replace('This line has none
         this   line has      four
      this line has one',  
'('||chr(13)||chr(10)||')( )+','\1_') RESULT from dual;  

This line has none 
_this   line has      four 
_this line has one
Re: Can this be done with a REGEXP [message #428618 is a reply to message #428616] Thu, 29 October 2009 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not work, "this line has four" get only one with your query.
In addition, it will no more work in Unix where there is no chr(13).

Regards
Michel
Re: Can this be done with a REGEXP [message #428642 is a reply to message #428618] Thu, 29 October 2009 04:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The closest I got is this:
SQL> set scan off
SQL> with yourtable as
  2  (
  3  select 'This line has none
  4      this line has four
  5   this line has one' theline
  6  from dual
  7  )
  8  select regexp_replace(theline, '^(\s)+','&nbpsp',1,0,'m') x
  9  from yourtable
 10  /

X
-------------------------------------------------------------------
This line has none
&nbpspthis line has four
&nbpspthis line has one
But I am hardly a regular expression expert.

MHE
Re: Can this be done with a REGEXP [message #428647 is a reply to message #428642] Thu, 29 October 2009 04:48 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not work for the same reason as jum's one: the line "has four" must start (as far I understand the question) with 4 "&nbsp;".

Regards
Michel
Previous Topic: how to restrict while inserting date information
Next Topic: Failed insertion of values into a table. (merged)
Goto Forum:
  


Current Time: Thu Sep 29 19:15:36 CDT 2016

Total time taken to generate the page: 0.22436 seconds