Postgresql parse string values to integers

Question

Is there a way in Postgres I can parse string values to integers? Basically I'm trying to query one table (let's call it table_one) using values from another table (table_two) in a character varying column.

Say SELECT char_column FROM table_two results in "2,4,6,8", I'd like to use this result in a second query as;

SELECT column FROM table_one WHERE some_id IN (2,4,6,8)

How can I get the string "2,4,6,8" to values 2,4,6,8 so as to be able to use it in the second query?

I've tried casting and to_number functions to no success.

as it is your question is not clear, please update it with schema, sample data and desired output: meta.stackoverflow.com/questions/271055/… — Haleemur Ali
– Haleemur Ali, Commented May 21, 2018 at 9:29

Nick Barnes · Accepted Answer · 2018-05-21 09:42:48Z

3

SELECT column
FROM table
WHERE other_column = ANY(string_to_array('2,4,6,8', ',')::INT[])

answered May 21, 2018 at 9:42

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clint_A Over a year ago

Thanks, I've been struggling with this for a while now.

Mabu Kloesen · Accepted Answer · 2018-05-21 10:36:28Z

1

Please try this:

SELECT column     FROM table    WHERE other_column IN (
                           SELECT NULLIF(i,'')::int 
                             FROM regexp_split_to_tables('2,4,6,8',',') t(i)
                         )

Explanation:

The part regexp_split_to_tables('2,4,6,8',',') will split the string into a table. Then you cast it into integer.

Hopefully it will help you.

answered May 21, 2018 at 10:36

Mabu Kloesen

1,3687 silver badges8 bronze badges

Collectives™ on Stack Overflow

Postgresql parse string values to integers

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related