3

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.

  • In the first place, why are you storing numbers as text? Furthermore, you could replace the [] with {} and then cast it to numeric[]. – dezso Oct 19 '15 at 8:35
5

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[])
-4

Simply,

from

123, 124

to

'123','124'

You can use String_agg function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.