Postgresql – how to select records from two columns where one column consist word from second column

postgresqlquery

In postgres I want to select rows where column1 matches column2 for corresponding row and returns result having same values as well as records consisting word from column1.

select * from table_name where "column1" ILIKE "column2";

i am using above query but it returns exact matches, it should also return rows consisting words from column1 in column2.

column1 | column2
hello   | hello
please  | please guys
help    | help me
query   | website

my above query returns

column1
hello

i want it to return

column1
hello
please
help

Best Answer

Do you need it to be case sensitive? If not try using LIKE

Create Table #Test
(
ColA Varchar(100)
,ColB Varchar(100)
)

Insert Into #Test
Values ('TEST', 'THIS IS A TEST')
,('String', 'This Also has word string in it')
,('ABC','DEF')
,('123','456')


Select      *
From        #Test 
Where       ColB Like '%' + ColA + '%'

Related Solutions

Sql-server – Equality query on NVARCHAR column yields multiple results in SQL Server 2012

The collation determines the comparison semantics.

If I try

CREATE TABLE [word](
    [id] [int] IDENTITY(0,1) NOT NULL,
    [value] [nvarchar](255) COLLATE Latin1_General_100_CI_AS NULL    
 );

It only returns ἀπὸ.

Changing the suffix to AI for accent insensitive returns ἀπό also.

On my install I have tried every collation and 1526 return 1 (presumably AS and BIN collations), 1264 return 2 rows (presumably AI) and 1095 return 8.

From a quick look through this last group looks to include all the SQL collations and 90 collations whereas all the 100 ones are in the first 2 groups so I presume this is some issue that has been fixed in the 2008 batch of collations. (See What's New in SQL Server 2008 Collations)

Script to try this yourself

DECLARE @Results TABLE
(
Count INT,
Collation SYSNAME
)

SET NOCOUNT ON;
DECLARE @N SYSNAME;
DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR 
SELECT name
FROM sys.fn_helpcollations();
OPEN @C1;
FETCH NEXT FROM @C1 INTO @N ;
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT @Results
  EXEC('SELECT COUNT(*), ''' + @N + ''' from word where value = N''ἀπὸ'' COLLATE ' + @N)
  FETCH NEXT FROM @C1 INTO @N ;
END

SELECT *
FROM @Results
ORDER BY Count DESC

Postgresql – GROUP BY and ORDER BY problem

First off, your PRIMARY KEY spanning two varchar(2000) columns seems extremely expensive. If you use your PK for anything else I suggest a surrogate PK (use a serial column) and add a UNIQUE constraint to enforce uniqueness on (cid, name, synonym).

If one of your varchar columns actually uses the maximum length you would exceed the maximum size for an index entry. See:

Character varying index overhead & length limit

I guess what you want is this, because it would make sense:

SELECT DISTINCT ON (c.name)
       c.name, min(s.synonym) AS min_synonym, s.cid
     , ts_rank(s.tsv_syns, c.lexemes, 16) AS rnk
     , count(*) AS ct
FROM   synonyms_all_gin_tsvcolumn s
JOIN   cmap5                      c ON c.lexemes @@ s.tsv_syns
GROUP  BY c.name, rnk, s.cid
ORDER  BY c.name, rnk DESC, ct DESC;

I use explicit [INNER] JOIN with attached join condition replacing your CROSS JOIN plus WHERE clause. It's generally considered superior (easier to read and debug). I also use rnk as column name to avoid the basic function name rank as identifier.
Group the results per c.name that have the same rnk and s.cid, take min(s.synonym) (for lack of definition in the question), and count(*) the peers per group.
Reduce to one row per c.name with DISTINCT ON (Postgres specific extension of SQL standard DISTINCT), taking the highest rank first and, within same rank, the highest peer count. See:
Select first row in each GROUP BY group?

Combining GROUP BY and DISTINCT ON this way in one query level is possible since DISTINCT or DISTINCT ON are applied after GROUP BY.

Best Answer

Related Solutions

Sql-server – Equality query on NVARCHAR column yields multiple results in SQL Server 2012

Postgresql – GROUP BY and ORDER BY problem

Related Question