How to Pass number value to a varchar field [message #678498] |
Mon, 09 December 2019 09:51 |
asadkhan_2
Messages: 36 Registered: January 2008
|
Member |
|
|
Hi,
I have a requirement that i have a column with data type varchar 2 and number values are being stored in that number. i need the highest value from that column which i am able to get from below query but now i need to pass this query in where clause to the same varchar column.
select MAX (TO_NUMBER (REGEXP_SUBSTR (colname, '\d+'))) colnameFROM tab
now i have below requirement
colname=select MAX (TO_NUMBER (REGEXP_SUBSTR (colname, '\d+'))) colname FROM tab where id=@id);
Thanks
|
|
|
|
|
|
Re: How to Pass number value to a varchar field [message #678504 is a reply to message #678498] |
Mon, 09 December 2019 11:18 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
with sample_data as (
select '100,31,12345,17,55' num_list from dual union all
select '100,31,-12345,17,55' num_list from dual
)
select num_list,
xmlcast(xmlquery(concat(concat('max((',num_list),'))') returning content) as number) max_num
from sample_data
/
NUM_LIST MAX_NUM
------------------- ----------
100,31,12345,17,55 12345
100,31,-12345,17,55 100
SQL>
SY.
|
|
|