Home » SQL & PL/SQL » SQL & PL/SQL » How to Pass number value to a varchar field
How to Pass number value to a varchar field [message #678498] Mon, 09 December 2019 09:51 Go to next message
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 #678499 is a reply to message #678498] Mon, 09 December 2019 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: How to Pass number value to a varchar field [message #678500 is a reply to message #678499] Mon, 09 December 2019 10:17 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok, so what is the problem?
Re: How to Pass number value to a varchar field [message #678502 is a reply to message #678498] Mon, 09 December 2019 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

In the end, if you don't feedback in your topics you'll not be surprised to not have more help, above all when you don't follow the forum guide.

Re: How to Pass number value to a varchar field [message #678504 is a reply to message #678498] Mon, 09 December 2019 11:18 Go to previous message
Solomon Yakobson
Messages: 2977
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.
Previous Topic: Rows into Columns (2 merged)
Next Topic: Materialized Views Cannot set with refresh Fast "Error"
Goto Forum:
  


Current Time: Fri Aug 07 21:57:17 CDT 2020