How to convert numeric values to text in sql for select query

Question

I have string values like this

'[123, 124]'

I could trim the value using below command

select trim('[123, 124]', '[]'); --returning '123, 124' as text

I want to pass the value above as

select * 
from mytable 
where numeric_column in (select trim('[123, 124]', '[]'));

I can understand, numeric_column is numeric. But the inner query is returning the data as text. I could not convert the inner query to numeric since it has a comma. If I want to convert the result to '123', '124', I could run the following command and get the expected result:

select * 
from mytable 
where numeric_column::text in (
 select func_to_change(select trim('[123, 124]', '[]'))
);

How can I achieve this? What is the func_to_change logic I need to write?

Note: I am using Postgresql 9.1.

• András Váczi: In the first place, why are you storing numbers as text? Furthermore, you could replace the [] with {} and then cast it to numeric[].

Answer 1

Once you have removed the [ and ] you can use string_to_array() to convert the list to an array of integers. This can be used directly in the where clause:

select * 
from mytable 
where numeric_column = ANY(string_to_array(trim('[123, 124]', '[]'),',')::numeric[])

Answer 2

Simply,

from

123, 124

to

'123','124'

You can use String_agg function