Where, contains, like, index, in:?

[bollibompa]

Hi,

I have a column which contains different codes separated by a space:

Column A

A20B3 B35XX Z70A2 A22VV

C30B3 B35XX Z70A2 C34VV

C30B3 B35XX Z70A2 A24VV

I now like to keep the rows where this column include codes beginning with A2

If I use "where column A contains A2" the two first rows will be kept which is uncorrect since no code in row 2 start with A2.

If I use "where column A in:A2" it will keep the first one, but the third row also have a code beggining with A2 which I want to keep.

Thanks for any help to solve this!

Thomas

[Astounding]

The trick to solving this easily and quickly (not searching every word in the string, avoiding parsing functions) is to append a blank at the beginning:

where index(' ' || columnA, ' A2') > 0;

[bollibompa]

Thanks!

Could I modify this index-argument if I want in the same search select several codes for example, codes that starts with A2 and C2?

/Thomas

[Astounding · Accepted solution]

No, you would really have to repeat the search:

where (index(' ' || columnA, ' A2') > 0) or (index(' ' || columnA, ' C2') > 0);

The more you add, the closer to get to having to learn parsing functions instead.

[bollibompa]

Thanks for your replies!

[PGStats]

where column =: "A2";