Home » SQL & PL/SQL » SQL & PL/SQL » How to get decimal value from string and convert (11g)
How to get decimal value from string and convert [message #670332] Thu, 28 June 2018 03:18 Go to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
Hi,

Thank in advance.I have a string like 'abc 50-30.5-20kg' and requirement is to convert it to 'abc 50000-30500-20000gm'.if the string is not having any decimal value then no need to convert. Please suggest.

Thanks

[Updated on: Thu, 28 June 2018 03:56]

Report message to a moderator

Re: How to get decimal value from string and convert [message #670333 is a reply to message #670332] Thu, 28 June 2018 04:23 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
INSTR.
Re: How to get decimal value from string and convert [message #670334 is a reply to message #670333] Thu, 28 June 2018 04:32 Go to previous messageGo to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
it should be dynamic. that string can be anything. like 'abc 0.5kg' or 'xyz 10.5-20.5kg' or 'pqr 10.5-5-2.5kg'
Re: How to get decimal value from string and convert [message #670335 is a reply to message #670334] Thu, 28 June 2018 04:39 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Literally anything?
Or are there some constraints on what you can have?
Are numbers always separated with -?
Do a set of numbers always have a space before them?
Do they always end in kg?
Re: How to get decimal value from string and convert [message #670336 is a reply to message #670335] Thu, 28 June 2018 05:02 Go to previous messageGo to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
it can be anything. when the string will have decimal value then i have to convert . when the string will have multiple number then it will separated by -. it basically shows the product range between 0.5-1kg . product can be 'prod 0.5kg' or 'prod1 2-3.5-5.5kg'. the measurement of strength of the product will be at the end of the string i.e kg. when i will convert i have to convert the full rang of product not only decimal value.

it is not always kg but i have to convert only kgs.
Re: How to get decimal value from string and convert [message #670337 is a reply to message #670336] Thu, 28 June 2018 05:06 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I think that you may need to work on your data model.
Re: How to get decimal value from string and convert [message #670338 is a reply to message #670336] Thu, 28 June 2018 05:10 Go to previous messageGo to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
the 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
Re: How to get decimal value from string and convert [message #670339 is a reply to message #670338] Thu, 28 June 2018 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
And are multiple numbers in the strength bit always separated by -?
Re: How to get decimal value from string and convert [message #670340 is a reply to message #670339] Thu, 28 June 2018 05:12 Go to previous messageGo to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
yes.
Re: How to get decimal value from string and convert [message #670342 is a reply to message #670340] Thu, 28 June 2018 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many decimal places can there be?
Re: How to get decimal value from string and convert [message #670343 is a reply to message #670342] Thu, 28 June 2018 05:48 Go to previous messageGo to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
two decimal places


Re: How to get decimal value from string and convert [message #670345 is a reply to message #670338] Thu, 28 June 2018 06:33 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
tikun wrote on Thu, 28 June 2018 05:10
the 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.
Re: How to get decimal value from string and convert [message #670347 is a reply to message #670345] Thu, 28 June 2018 06:52 Go to previous messageGo to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
sorry i cant change the product name. it is being used based on strength .based on strength product is different. from this uniqueness we can identify which has more demand in market.
Re: How to get decimal value from string and convert [message #670348 is a reply to message #670345] Thu, 28 June 2018 07:11 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
EdStevens wrote on Thu, 28 June 2018 12:33
tikun wrote on Thu, 28 June 2018 05:10
the 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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 #670355 is a reply to message #670350] Thu, 28 June 2018 08:59 Go to previous messageGo to next message
tikun
Messages: 8
Registered: June 2018
Junior Member
Hi SY,

Thanks a lot. Its working for me.
Re: How to get decimal value from string and convert [message #670380 is a reply to message #670348] Fri, 29 June 2018 08:47 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
Roachcoach wrote on Thu, 28 June 2018 07:11
EdStevens wrote on Thu, 28 June 2018 12:33
tikun wrote on Thu, 28 June 2018 05:10
the 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.
Yes, it always amazes me when duh-velopers start banging their heads against the wall trying to solve a problem like this, that is of their own creation for not following sound and proven design principles, and then insisting that their way is sooooo much better/easier. Kind of like EAV designs.
Re: How to get decimal value from string and convert [message #670382 is a reply to message #670380] Fri, 29 June 2018 09:24 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
EdStevens wrote on Fri, 29 June 2018 14:47
Kind 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 Shocked

[Edit: typo ]

[Updated on: Fri, 29 June 2018 09:44]

Report message to a moderator

Re: How to get decimal value from string and convert [message #670409 is a reply to message #670382] Tue, 03 July 2018 06:19 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
gazzag wrote on Fri, 29 June 2018 10:24
EdStevens wrote on Fri, 29 June 2018 14:47
Kind 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 Shocked

[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.
Previous Topic: Extract domain name from email address field
Next Topic: Help required on ranking logic
Goto Forum:
  


Current Time: Fri Sep 18 08:24:24 CDT 2020