1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

10 digit number

Discussion in 'SQL PL/SQL' started by karthik.rajashekaran, Aug 26, 2016.

  1. karthik.rajashekaran

    karthik.rajashekaran Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    Please help me to write sql query for the below puzzle

    Find a 10-digit number where the first digit is how many zeros in the number, the second digit is how many 1s in the number etc. until the tenth digit which is how many 9s in the number
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    675
    Likes Received:
    136
    Trophy Points:
    830
    Location:
    Russian Federation
    for example :
    Code (SQL):

    accept your_number NUMBER prompt 'Enter your number:'
    WITH
      pos_num AS
      (
      SELECT
      level-1 pos,
      nvl(LENGTH(REPLACE(translate(REPLACE (to_char(&your_number),to_char(level-1),'@'),'!'||to_char(&your_number),'!'),'@',to_char(level-1))),0) cnt
      FROM dual
      CONNECT BY level <=10
      )
    SELECT
      to_char(&your_number)  your_val,
      xmlagg(
      xmlelement("n",pn.cnt)
      ORDER BY pn.pos
      ).EXTRACT('//text()').getStringVal()  map_num
    FROM
      pos_num pn;


    SQL>
    YOUR_VAL       MAP_NUM
    -------------         --------------------
    1223344557899  0122220112
     
     
    Last edited: Aug 26, 2016
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,459
    Likes Received:
    355
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If numwidth isn't set large enough the script won't work:

    Code (SQL):
    SQL> @count_number_ex
    SP2-0310: unable TO OPEN file "count_number_ex.sql"
    SQL> @count_numbers_ex
    Enter your NUMBER:1223344557899


    YOUR_VAL
    -------------
    MAP_NUM
    --------------------------------------------------------------------------------
    1223300000000
    8122000000


    SQL> SET numwidth 20
    SQL> @count_numbers_ex
    Enter your NUMBER:1223344557899


    YOUR_VAL
    -------------
    MAP_NUM
    --------------------------------------------------------------------------------
    1223344557899
    0122220112
     
  4. Sam Short

    Sam Short Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Memphis
    Last edited: Apr 5, 2017