|
|
|
|
|
|
|
|
|
|
|
|
|
Re: How to get decimal value from string and convert [message #670348 is a reply to message #670345] |
Thu, 28 June 2018 07:11 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
EdStevens wrote on Thu, 28 June 2018 12:33tikun wrote on Thu, 28 June 2018 05:10the field is having product name + strength + measure . product name wont have any numerical value only strength will have. example 'prod 0.5kg' or 'prod 2-3.5-5.5kg'.
above prod is the product name, 2-3.5-5.5 iss strength value and kg is measure
You definitely need to work on your data model. The current model violates every design principle known to man.
"But it's so much easier for the devs! Relational is legacy maaaaaaan."
Or some such jibberjabber was how I imagine that conversation went.
[Updated on: Thu, 28 June 2018 07:11] Report message to a moderator
|
|
|
Re: How to get decimal value from string and convert [message #670350 is a reply to message #670334] |
Thu, 28 June 2018 07:51 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
XMLQUERY solution:
with data as (
select 'abc 50-30.5-20kg' str from dual union all
select 'abc 0.5kg' from dual union all
select 'xyz 10.5-20.5kg' from dual union all
select 'pqr 10.5-5-2.5kg' from dual
)
select str,
substr(str,1,instr(str,' ')) ||
xmlcast(
xmlquery(
'if (ora:matches(.,"[-]"))
then
string-join(
for $i in ora:tokenize(.,"-")
return xs:string(xs:decimal($i) * 1000),
"-"
)
else
xs:string(xs:decimal(.) * 1000)'
passing substr(str,instr(str,' '),length(str) - instr(str,' ') - 1)
returning content
)
as varchar2(25)
) || 'gm' new_str
from data
/
STR NEW_STR
---------------- ------------------------
abc 50-30.5-20kg abc 50000-30500-20000gm
abc 0.5kg abc 500gm
xyz 10.5-20.5kg xyz 10500-20500gm
pqr 10.5-5-2.5kg pqr 10500-5000-2500gm
SQL>
SY.
|
|
|
|
|
|
Re: How to get decimal value from string and convert [message #670409 is a reply to message #670382] |
Tue, 03 July 2018 06:19 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
gazzag wrote on Fri, 29 June 2018 10:24EdStevens wrote on Fri, 29 June 2018 14:47Kind of like EAV designs.
*growls*
In a similar vein, one of my developers found out recently that 12c allows 128 character length identifiers. He started designing a customer survey application where the table names were actually the questions
[Edit: typo ]
It not only allows 128 character variable and column names (in 12.2 and greater) but it now allows varchar2 characters with a maximum size of 32K instead of 4K.
|
|
|