CODEWITHSUNDEEP
stringpostgresqlcastingbigint
Manasa
Manasa

Reputation: 1

How to convert 52:34 string value to bigint

How to cast a value 52:35 which is stored as a string in the DB and I want only 52 in BigInt value in PostgreSQL DB as a query.

I tried with this following query

select cast(substr(a,1,strpos(a,':')-1) AS bigint) as value from abc

which returned me an error "negative substring length not allowed"

Upvotes: 0

Views: 321

Answers (2)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

You could use split_part(string text, delimiter text, field int)

postgres=# select split_part('52:35', ':', 1)::bigint;
 split_part
------------
         52

postgres=# select split_part('52', ':', 1)::bigint;
 split_part
------------
         52

Upvotes: 1

Jim Garrison
Jim Garrison

Reputation: 86744

That query will fail when it encounters a value that does not contain a colon :. Use a case...when...else...end construct to attempt the extraction only when the value contains a colon. Something like (untested)

CASE WHEN strpos(a,':') > 0 
    THEN cast(substr(a,1,strpos(a,':')-1) AS bigint
    else null
END

For the else case, substitute whatever you need. There might also be a way to use split_part(...) instead of the above, but I had trouble finding documentation saying what happens if the delimiter is not present.

Upvotes: 1

Related Questions