CODEWITHSUNDEEP
sqlstringpostgresql
Tito
Tito

Reputation: 671

How to filter results to find a specific string value

I am searching for a particular value in the string, each value in this string designates a clause which looks like this

K1,K2,K3,K114,K22,K110,...

Standard results

enter image description here

Lets say I want to search for people who have K1 When I use the following filter in my where section

where q2.klause like '%K1%'

it will get me all people who have a k1, however if someone has K11 for instance without the K1 it will also add it which is not what I want!

So I tried this approach

where q2.klause like '%K1'

enter image description here

Sadly it also doesnt work because it only gets people who only have K1 alone, unshared without clauses.

I want anybody who has a K1 whether they have/not other klauses beside it! 

UIDs    Klause
6548    K1,K35,K37,K4
48      K1,K34
486     K1,K14
8974    K1
456568  K11,K12,K2
8814    K2,K14,K34
6248    K14,K2
2236    K1,K35,K37,K4
547     K2
397812  K2
586     K2,K11
1358    K1,K13,K14
5856    K1,K14
9872    K1,K14
64789   K1,K14
22344   K1,K14,K35,K37
4788    K1,K14
4587    K1,K14,K35,K37
14561   K11,K12,K14,K2
232156  K1,K14
156     K1,K114
475     K11,K12,K14,K2
45645   K13,K14
456454  K13,K14

In this case there are 14 people who have K1. This is the final answer required! Please be aware this should be also done with other klauses aswell.

Upvotes: 3

Views: 44

Answers (4)

user330315
user330315

Reputation:

I would convert the column value to an array and use the contains operator:

where string_to_array(q2.klause,',') @> 'K1'

alternatively: 

where 'K1' = any(string_to_array(q2.klause,','))

Upvotes: 0

Bruno Souza
Bruno Souza

Reputation: 218

WHERE q2.klause LIKE '%K1' 
OR q2.klause LIKE '%K1,%'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You should fix your data model. But you can use like this way:

where ',' || q2.klause || ',' like '%,K1,%'

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 247270

Use a regular expression:

WHERE q2.klause ~ '\mk1\M'

\m matches the beginning of a word and \M the end of a word.

Upvotes: 3

Related Questions