7 Replies Latest reply on Jun 17, 2019 10:38 AM by user5345

    Convert char to number

    user5345

      Dear All,

       

      I have table A with one column as char.

       

      storing data as 1,2,4,7,12

       

      I need to convert to number out put.

       

      The output value has to passed to different table column in number.

       

      Please guide me

       

      Thanks,

        • 1. Re: Convert char to number
          AndrewSayer

          user5345 wrote:

           

          Dear All,

           

          I have table A with one column as char.

           

          storing data as 1,2,4,7,12

           

          I need to convert to number out put.

           

          The output value has to passed to different table column in number.

           

          Please guide me

           

          Thanks,

          Re: 2. How do I ask a question on the forums?

          Its not clear if you have one row with multiple values all concatenation together to form an impossible to work with string or if you have many rows.

          Please share DDL and insert statements to produce what you have.

           

          Is this a one off excercise as you are fixing your data model or will you be doing this multiple times and both tables need to be kept in sync?

          • 2. Re: Convert char to number
            user5345

            1) Query

            2 ) Query

             

              select xpm.pharmacy_code, xpm.description, sum(qoh) qoh

              from   stock_master xsm, pharmacy_master xpm

              where  itemcode = '31PGDE0216'

              and    xpm.pharmacy_key = xsm.pharmacy_key

              and    xpm.pharmacy_key in (1,2,4,7,12,13,15,16,17,18,19,24,25,26)

              group by xpm.pharmacy_code, xpm.description, xpm.pharmacy_key

             

            First query give me recods which is varchar and then need to pass this values in the 2nd query which in number formart.

             

            Please guide

            • 3. Re: Convert char to number
              user5345

              urgent pls any one can help

              • 4. Re: Convert char to number
                Manik

                B'lve me if you say "urgent" nobody will answer ;-)

                 

                You are facing consequences of wrong design as I understand. Data is not normalized.

                 

                Anyways, now that you are already into that......

                 

                A small hint, tokenize the string and then use for comparison, there are many techniques to split and compare... One of them is like :

                 

                WITH dataset AS (SELECT '1,2,3,4,5,6' col FROM DUAL)

                select column_value col from dataset, XMLTABLE (REPLACE (col, '''' || col || ''''));

                 

                Cheers,

                Manik.

                • 5. Re: Convert char to number
                  cormaco

                  If you have only numbers separated by commas you can do that:

                  with xxmnc_pos_stock(location_code,pharmacy_key) as (
                      select 'ABU DHABI','1,2,4,7,12,13,15,16,17,18,19,24,25,26' from dual
                  )
                  select pkey 
                  from 
                      xxmnc_pos_stock,
                      xmltable(pharmacy_key passing pharmacy_key columns pkey number path '.')
                  where location_code = 'ABU DHABI'
                  
                  
                        PKEY
                  ----------
                           1
                           2
                           4
                           7
                          12
                          13
                          15
                          16
                          17
                          18
                          19
                          24
                          25
                          26
                  
                  

                   

                  For other requirements there are other solutions possible.

                  • 6. Re: Convert char to number
                    BluShadow

                    user5345 wrote:

                     

                    urgent pls any one can help

                     

                     

                    Hang on, I'll just check my "urgent" calendar.... erm... I can fit you in Next Wednesday, about 3.30pm.

                     

                    Seriously, nothing is "urgent" here.

                    "Urgent" issues are where there is a problem on a live database/server/application, that is causing the company to lose money or potentially causing a data breach etc.  For such issues you should be contacting Oracle Support directly using your Oracle Support Identifiers, over on support.oracle.com and not here on the public community manned by volunteers with their own day jobs.

                     

                    Avoid saying your needs are urgent.  Everyone would like their questions answered as soon as possible, but saying it's urgent is you suggesting that those volunteers should drop what they are doing to help you, and that your issue is somehow more important than other people who have already asked questions and are patiently waiting.  That's just rude.

                     

                    Cormaco has shown one way to split your data. There are others ways, as this is traditionally known as the "varying in clause" issue, which is covered in the FAQ:

                     

                    Re: 7. List of values in an IN clause?

                    • 7. Re: Convert char to number
                      user5345

                      ok big boss, thanks