CODEWITHSUNDEEP
sqlpostgresql
Johnnerz
Johnnerz

Reputation: 1425

How to create an integer from a certain string cell in PSQL

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
16266|   24000|
16267|  -12500|
16268|   25000| abc:185729000003412
16269|   25000| abc:185730000003412

What I am trying to get is a query or a function that will return the ids of the duplicate rows that have the same amount and the same modulus (%100000000) of the number in the string in the reference column.

The only cells in the reference column I am interested in will all have 'abc:' before the whole number, and nothing after the number. I need some way to convert that final field (string) into a int so I can search for the modulus of that number

Here is the script I will run once I get the reference field converted into a number without the 'abc:'

CREATE TEMP TABLE tableA (
id int,
amount int,
referenceNo bigint)

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

Basically, how do I convert the reference field (abc:185729000003412) to an integer in PSQL (185729000003412 or 3412)?

Upvotes: 0

Views: 196

Answers (1)

Yurii Dolhikh
Yurii Dolhikh

Reputation: 1431

Assuming that reference id is always delimited by :

split_part(Reference, ':', 2)::integer

should work.

Edit:

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.

Upvotes: 2

Related Questions