postgresql conditionally generating value for each row

comments and questions about working with SQL Server and PostgreSQL
Post Reply
Posts: 1
Joined: Sun Jun 23, 2019 7:44 am

Sun Jun 23, 2019 9:00 am


I need to match 'words' against 'name' values and wanted to get the names with more matches on the top of result. I have tried to use the below however this doesn't seem to work:

Code: Select all

declare matches integer := 0
select if "word1" ~* name then matches := matches + 1 end if
       if "word2" ~* name then matches := matches + 1 end if
       as M_count from links order by M_count;
How do I achieve this?

Posts: 12
Joined: Sat Jun 22, 2019 7:05 pm

Sun Jun 23, 2019 9:03 am


The best way would be to convert the boolean to a INTEGER and then SUM them up, for example:

Code: Select all

select l.*,
       ( ("word1" ~* name)::int +
         ("word2" ~* name)::int +
         . . .
       ) as matches         
from links l
order by matches desc;
Post Reply

Social Media