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:
    626
    Likes Received:
    129
    Trophy Points:
    810
    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,351
    Likes Received:
    348
    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 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Memphis
    What is the point of this? Just curious.