Postgresql convert html to text

Use xpath

Feed your database with XML datatype, not with "second class" TEXT, because is very simple to convert HTML into XHTML [see HTML-Tidy or standard DOM's loadHTML[] and saveXML[] methods].

! IT IS FAST AND IS VERY SAFE !

The commom information retrieval need, is not a full content, but something into the XHTML, so the power of xpath is wellcome.

Example: retrive all paragraphs with class="fn":

  WITH needinfo AS [
    SELECT *, xpath['//p[@class="fn"]//text[]', xhtml]::text[] as frags
    FROM t 
  ] SELECT array_to_string[frags,' '] AS my_p_fn2txt
    FROM needinfo
    WHERE array_length[frags , 1]>0
  -- for full content use xpath['//text[]',xhtml]

regex solutions...

I not recomend because is not an "information retrieval" solution... and, as @James and others commented here, the regex solution is not so safe.

I like "pure SQL", for me is better than use Perl [se @Daniel's solution] or another.

 CREATE OR REPLACE FUNCTION strip_tags[TEXT] RETURNS TEXT AS $$
     SELECT regexp_replace[
        regexp_replace[$1, E'[?x]]*?[\s alt \s* = \s* [[\'"]] [[^>]*?] \2] [^>]*? >', E'\3'], 
       E'[?x][< [^>]*? >]', '', 'g']
 $$ LANGUAGE SQL;

See this and many other variations at siafoo.net, eskpee.wordpress, ... and here at Stackoverflow.

Author - Kailash

Problem : How to create a function in Postgres that will remove HTML tags from a piece of text?

Solution : Create function in postgres :

CREATE OR REPLACE FUNCTION strip_tags[TEXT] RETURNS TEXT AS $$
    SELECT regexp_replace[$1, ']*>', '', 'g']
$$ LANGUAGE SQL;

How to use :

SELECT strip_tags['
Kailash
Kumar'];

Output: KailashKumar

Note: This function will remove all the content between < and > symbol. If HTML tags are not proper then your text may also get removed so check your HTML before parsing it through this function.

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at for your Insurance, eLearning and Salesforce applications.

ascii[string] int ASCII code of the first byte of the argument ascii['x'] 120 btrim[string text [, characters text]] text Remove the longest string consisting only of characters in characters [a space by default] from the start and end of string btrim['xyxtrimyyx', 'xy'] trim chr[int] text Character with the given ASCII code chr[65] A convert[string text, [src_encoding name,] dest_encoding name] text Convert string to dest_encoding. The original encoding is specified by src_encoding. If src_encoding is omitted, database encoding is assumed. convert[ 'text_in_utf8', 'UTF8', 'LATIN1'] text_in_utf8 represented in ISO 8859-1 encoding decode[string text, type text] bytea Decode binary data from string previously encoded with encode. Parameter type is same as in encode. decode['MTIzAAE=', 'base64'] 123\000\001 encode[data bytea, type text] text Encode binary data to different representation. Supported types are: base64, hex, escape. Escape merely outputs null bytes as \000 and doubles backslashes. encode[ E'123\\000\\001', 'base64'] MTIzAAE= initcap[string] text Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. initcap['hi THOMAS'] Hi Thomas length[string] int Number of characters in string length['jose'] 4 lpad[string text, length int [, fill text]] text Fill up the string to length length by prepending the characters fill [a space by default]. If the string is already longer than length then it is truncated [on the right]. lpad['hi', 5, 'xy'] xyxhi ltrim[string text [, characters text]] text Remove the longest string containing only characters from characters [a space by default] from the start of string ltrim['zzzytrim', 'xyz'] trim md5[string] text Calculates the MD5 hash of string, returning the result in hexadecimal md5['abc'] 900150983cd24fb0 d6963f7d28e17f72 pg_client_encoding[] name Current client encoding name pg_client_encoding[] SQL_ASCII quote_ident[string] text Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary [i.e., if the string contains non-identifier characters or would be case-folded]. Embedded quotes are properly doubled. quote_ident['Foo bar'] "Foo bar" quote_literal[string] text Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. quote_literal[ 'O\'Reilly'] 'O''Reilly' regexp_replace[string text, pattern text, replacement text [,flags text]] text Replace substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. regexp_replace['Thomas', '.[mN]a.', 'M'] ThM repeat[string text, number int] text Repeat string the specified number of times repeat['Pg', 4] PgPgPgPg replace[string text, from text, to text] text Replace all occurrences in string of substring from with substring to replace[ 'abcdefabcdef', 'cd', 'XX'] abXXefabXXef rpad[string text, length int [, fill text]] text Fill up the string to length length by appending the characters fill [a space by default]. If the string is already longer than length then it is truncated. rpad['hi', 5, 'xy'] hixyx rtrim[string text [, characters text]] text Remove the longest string containing only characters from characters [a space by default] from the end of string rtrim['trimxxxx', 'x'] trim split_part[string text, delimiter text, field int] text Split string on delimiter and return the given field [counting from one] split_part['abc~@~def~@~ghi', '~@~', 2] def strpos[string, substring] int Location of specified substring [same as position[substring in string], but note the reversed argument order] strpos['high', 'ig'] 2 substr[string, from [, count]] text Extract substring [same as substring[string from from for count]] substr['alphabet', 3, 2] ph to_ascii[string text [, encoding text]] text Convert string to ASCII from another encoding [only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings] to_ascii['Karel'] Karel to_hex[number int or bigint] text Convert number to its equivalent hexadecimal representation to_hex[2147483647] 7fffffff translate[string text, from text, to text] text Any character in string that matches a character in the from set is replaced by the corresponding character in the to set translate['12345', '14', 'ax'] a23x5