CODEWITHSUNDEEP
sqlregexpostgresql
Andrus
Andrus

Reputation: 27931

How to strip comments starting at ** line form text in sql

Postgres 8.3+ database text column contains possible multi line expression.

After expression there and be empty lines and comment area. Comment area starts with ** characters at start of line. There can be only empty lines in end of expression

Examples

1+
2
** this
is comment

With trailine extra newline:

1+
2

** this
is comment and newline after expression

How to strip empty lines and comments from expression? Empty lines inside expression shoudl replaced to spaces. Empty lines and comments at end of expression should stripped. Result should be 1+ 2 in both cases. If there is only comment, empty string should returned.

I tried method below but this does not replace newlines inside expression with spaces. How to fix ?

CREATE OR REPLACE FUNCTION public.RemoveComment(value text) RETURNS text AS
$BODY$
select case when tulem like '**%' then ''
  else tulem end
from (  
SELECT trim(lower( Translate(   regexp_replace($1, E'\n\\*\\*.*', '', 'g'),E'\n\r', '')    )) as tulem
) tulem;

$BODY$ language sql immutable;

Upvotes: 0

Views: 206

Answers (2)

Jakub Kania
Jakub Kania

Reputation: 16487

You should use the replace twice if you want to remove comments and replace newlines with spaces inside text:

regexp_replace(regexp_replace($1, '((\n*)(\*\*.*)?)$', ''),'\n',' ')

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656676

Can't test in Postgres 8.3, that's just too old and not available to me any more. But this should work - it does in Postgres 9.3.

SELECT regexp_replace($1, '[ \n]*\*\*.*$', '');

With standard_conforming_strings = on! More:

SQL Fiddle.

Explain

[ \n\r] .. character class of space, newline and carriage return. You may not need \r, depending on your strings ..
* .. any number or times
\*\* .. two literal stars
.* .. any characters, any number of time
$ .. end of string

Upvotes: 1

Related Questions