Question 89693 in PostgreSQL Question posted in
The official documentation can be found here.

How to display only searched string in column in postgresql

I want to only display searched string from a table, as example this is my table:

Table name: guidelines
id  content 
1   An individual is accused “of” a crime, not “with” or “for” a crime. Accused, often as “the accused”, refers to the individual or individuals standing trial. EXAMPLES: The prosecutor accused the politician of bribery. The accused politician stood trial for bribery. See alleged, charged, suspected.
2   There were a lot of people getting accused on this particular town.

If I use search query to search for "accused", it will show the full result:

SELECT content FROM "guidelines" WHERE "content" 'ILIKE' '%accused%';

Result:

content 
An individual is accused “of” a crime, not “with” or “for” a crime. Accused, often as “the accused”, refers to the individual or individuals standing trial. EXAMPLES: The prosecutor accused the politician of bribery. The accused politician stood trial for bribery. See alleged, charged, suspected.

There were a lot of people getting accused on this particular town.

How can I only get the first matching string and followed by the data on the column, as example this is my goal:

content
Accused, often as “the accused”, refers to th...

accused on this particular to...

update: I updated the table and column name to make it better to differentiate table and column

Tags:

2

Answers


  1. 0

    In Postgresql, you can do that by using position function and substring function. see the following query as an example:

    SELECT
    id,
    substring(content, position ('accused' in content)) as matched
FROM
    guidelines
WHERE
    content LIKE '%accused%'
    Login or Signup to reply.
  2. 0

    Try this :

    SELECT substring(content from '%#"accused%#"%' for '#') from guidelines;

    each # is the place holder defined in the last part for '#' and need and aditional "

    So you have % and function will return what is found inside both placeholder. In this case is % or the rest of the string after accused

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search