Search code examples
sqloracledummy-data

How do I add unique incrementing "dummy" data to a query in Oracle SQL?

Let's say that I am working with a table and all that I want out of that actual table is a single column. I will want to use dummy values for additional columns, but they have to be unique and incrementing numbers.

I want a result something like this:

MAC_ADDR DUMMY_VALUE
64:16:7F:64:94:1A test-001
64:16:7F:0B:EB:9F test-002
64:16:7F:5C:99:3F test-003
64:16:7F:23:7C:C8 test-004
64:16:7F:3B:4A:A6 test-005

I've found solutions that seem close when searching through other similar questions, but those seemed to do weird things like repeating the same MAC once for each dummy value, for example. I just want a single row per MAC address, and a unique value in my dummy value columns.

Database programming outside of your standard queries is basically foreign to me.

I tried a query like this, but it had strange outputs with the previously mentioned repeating data and non-unique values.

SELECT MAC_ADDR, CONCAT('test-','001' + LEVEL) AS DUMMY_VALUE FROM MAC_ADDRESS_TABLE CONNECT BY LEVEL <= 5;

Solution

  • Use the ROWNUM pseudo-column and format it using TO_CHAR:

    SELECT MAC_ADDR,
       'test-' || TO_CHAR(ROWNUM, 'FM000') AS DUMMY_VALUE
FROM   MAC_ADDRESS_TABLE;

    Which, for the sample data:

    CREATE TABLE MAC_ADDRESS_TABLE ( MAC_ADDR ) AS
SELECT '64:16:7F:64:94:1A' FROM DUAL UNION ALL
SELECT '64:16:7F:0B:EB:9F' FROM DUAL UNION ALL
SELECT '64:16:7F:5C:99:3F' FROM DUAL UNION ALL
SELECT '64:16:7F:23:7C:C8' FROM DUAL UNION ALL
SELECT '64:16:7F:3B:4A:A6' FROM DUAL;

    Outputs:

    MAC_ADDR DUMMY_VALUE
    64:16:7F:64:94:1A test-001
    64:16:7F:0B:EB:9F test-002
    64:16:7F:5C:99:3F test-003
    64:16:7F:23:7C:C8 test-004
    64:16:7F:3B:4A:A6 test-005

    If you want the table ordered before numbering then you can use:

    SELECT MAC_ADDR,
       'test-' || TO_CHAR(ROWNUM, 'FM000') AS DUMMY_VALUE
FROM   (SELECT * FROM MAC_ADDRESS_TABLE ORDER BY mac_addr);

    or:

    SELECT MAC_ADDR,
       'test-'
       || TO_CHAR(ROW_NUMBER() OVER (ORDER BY mac_addr), 'FM000') AS DUMMY_VALUE
FROM   MAC_ADDRESS_TABLE;

    Which both output:

    MAC_ADDR DUMMY_VALUE
    64:16:7F:0B:EB:9F test-001
    64:16:7F:23:7C:C8 test-002
    64:16:7F:3B:4A:A6 test-003
    64:16:7F:5C:99:3F test-004
    64:16:7F:64:94:1A test-005

    fiddle