I have three certain columns in a table I am trying to query, say ID(char), Amount(bigint) and Reference(char). Here is a sample of a few entries from this table. The first two rows have no entry in the third column.
ID | Amount | Reference
16268| 25000| abc:185729000003412
16269| 25000| abc:185730000003412
CREATE TEMP TABLE tableA (
INSERT INTO tableA (id, amount, referenceNo) SELECT id, net_amount, longnumber%100000000 AS referenceNo FROM deposit_item
SELECT DISTINCT * FROM tableA WHERE referenceNo > 1 AND amount > 1
Assuming that reference id is always delimited by
split_part(Reference, ':', 2)::integer
If you want to match
abc: specifically - try this:
CASE WHEN position('abc:' in Reference) > 0 THEN split_part(Reference, 'abc:', 2)::integer ELSE 0 END
But you should indeed consider storing the
xxx: prefix separately.