CODEWITHSUNDEEP
oracle-databasereplaceplsqlsplit
Sim1
Sim1

Reputation: 532

Oracle PL-SQL Replace a charecter in the middle of a string

I have a stored procedure that retrieve all the data I need in the following format:

column 1 | column 2 | ....
text 1   | lorem ipsum doloret & sit amet | ....
text 2   | doloret  | ....
text 3   | sit amet, tu ne quesiri | ....

So, since the data will be displayed in a html table, i need to split the second column so it will take the same space (i.e. wrap text). I was thinking (c# oriented) that if I split the column 2 using space as delimiter and I replace the middle space with a '<br/>' it may works, but I have no idea on how to achieve this in PL-SQL (it is mandatory to put the login on the DB side for this project).

Many thanks in advance, Simone.

Upvotes: 0

Views: 1680

Answers (3)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

use regular expressions, e.g. the following replaces the space with <br/> after each word longer than 7 characters:

REGEXP_REPLACE(str, '([^ ]{7})( )', '\1<br/>')

and the next one after at least 7 characters:

REGEXP_REPLACE(str, '(.{7})( )', '\1<br/>')

Upvotes: 1

Dmitry.Samborskyi
Dmitry.Samborskyi

Reputation: 485

Why don't you just use replace(trim(column2), ' ', '<br/>') if you need wrap words by space

Upvotes: 0

Sim1
Sim1

Reputation: 532

I've solved it with a bit of creativity, but any better suggestion is appreciated:

substr(column2,1,instr(column2,' ',7)-1) || '<br/>' || substr(column2,instr(column2,' ',7)+1

Upvotes: 1

Related Questions