Search code examples
postgresqlprojection

Data projection abefore a particular word using Postgres?

I have a coloumn in Postgres which has text in it. I want to search for a particular word in that text and not display anything after that word Example :If the text is :"I am a very religious user of stack overflow. I always use it for reserach." So now I dont want to show anything after the word "always" HOW CAN THIS BE DONE?

The Expected O/P should be :"I am a very religious user of stack overflow. I" The word always is same but may or may not appear in the text. Thanks

Solution

  • You can use a regular expression:

    select regexp_replace(the_column, 'always.*', '')

    this will replace everything after (and including) the word always with nothing, but won't replace anything if the word is not found.

    If you also want to "catch" the same word in upper case or mixed case, just pass a flag to regexp_replace to work in a case-insensitive manner

    select regexp_replace(the_column, 'always.*', '', 'i')

    SQLFiddle example: http://sqlfiddle.com/#!15/d41d8/4307