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.

In the first place, why are you storing numbers as text? Furthermore, you could replace the [] with {} and then cast it to numeric[]. — András Váczi
– András Váczi, Commented Oct 19, 2015 at 8:35

András Váczi · Accepted Answer · 2015-10-19 14:59:28Z

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

edited Oct 19, 2015 at 14:59

András Váczi

31.8k13 gold badges103 silver badges152 bronze badges

answered Oct 19, 2015 at 9:51

user1822

Add a comment |

Muhammad Muazzam · Accepted Answer · 2015-10-19 07:04:34Z

-4

Simply,

from

123, 124

to

'123','124'

You can use String_agg function

edited Oct 19, 2015 at 7:04

answered Oct 19, 2015 at 0:10

Muhammad Muazzam

1459 bronze badges

Add a comment |

Stack Exchange Network

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

2 Answers 2

Hot Network Questions

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

2 Answers 2

Related

Hot Network Questions