You Asked

I had the following requirement where I need to get the below output

create table test_table1(xstring varchar2(20));

insert into test_table1 values('11111');
insert into test_table1 values('101011');
insert into test_table1 values('1000111');

xstring Result
------- --------
11111 MTWRF
10111 M-WRF
11011 MT-RF

I got this result using the following code:

select xstring,
decode(substr(xstring,1,1),'1','M',0,'-') ||
decode(substr(xstring,2,1),'1','T',0,'-') ||
decode(substr(xstring,3,1),'1','W',0,'-') ||
decode(substr(xstring,4,1),'1','R',0,'-') ||
decode(substr(xstring,5,1),'1','F',0,'-')
Result
from test_table1;

However I was given another requirement which requires the output as following (here the length of xstring varies):

xstring result
------- --------
11111 12345
101011 1-3-56
1000111 1---567

Could you give me a solution for this problem ?. The complexity is that in the second requirement the xstring is holding variable length strings unlike the first requirement where the length of the string is static i.e. 5. Please help me out . thanks.

Actually the length of the xstring can be anything and
for ex: if the input is 1000111
I need to get an output like 1---567.

for a string with input : 00000000011111
I anticipate an output : ---------1011121314

I need every occurence of 1 to be replaced by a number. Actually this is a barcode application that shows "1" if a person is present , I need to associate the day with it. And for every '0' which means absent , I just need to show '-' skipping that day.

I don't know the reason behind it but that is what they have asked for, you were absolutely right in giving your example but when we take a string '101011001' , it might be useful to identify the number of days a person was present i.e 5 because there are 5 1's , However to know what is the date , I guess that is the reason why they might have opted for this interpretation using numbers.

U are absolutely right, I think we shall close this issue because after what you have said , my requirement does'nt look to be a realistic scenario. I thing the substr idea that you gave should prove to be more useful. Thanks a lot. However is it possible using the SQL query to do such kind of replacement or do we need to write a pl/sql function for it

and Tom said...

we had a long back and forth on this - not because it was hard to answer, but because I thought the answer (the output) would be not useful at all - and the inputs would be useful for what they were trying to do...

And eventually, I convinced....

But, it is sort of a neat query, so.....


ops$tkyte%ORA10GR2> create table t(x varchar2(20));

Table created.

ops$tkyte%ORA10GR2> column column_value format a30
ops$tkyte%ORA10GR2> column scbp format a30
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values('11111');

1 row created.

ops$tkyte%ORA10GR2> insert into t values('101011');

1 row created.

ops$tkyte%ORA10GR2> insert into t values('1000111');

1 row created.

ops$tkyte%ORA10GR2> insert into t values('000000000111');

1 row created.


ops$tkyte%ORA10GR2> with data
  2  as
  3  (
  4  select rowid rid, x, to_number( substr(column_value,1,4)) lvl, substr( column_value,6,1) bit
  5    from t,
  6         table(
  7          cast(
  8           multiset( select to_char(level,'fm0000')|| '-' || substr(x,level,1)
  9                       from dual
 10                    connect by level <= length(x) ) as sys.odcivarchar2list ) )
 11  )
 12  select data.* , replace( sys_connect_by_path( decode( bit, '1', to_char(lvl), '-' ), ' ' ), ' ', '') scbp
 13    from data
 14  where lvl = length(x)
 15  start with lvl = 1
 16  connect by prior rid = rid and prior lvl+1 = lvl
 17  /

RID                X                           LVL B SCBP
------------------ -------------------- ---------- - ------------------------------
AAAnb3AAEAAAEAmAAA 11111                         5 1 12345
AAAnb3AAEAAAEAmAAB 101011                        6 1 1-3-56
AAAnb3AAEAAAEAmAAC 1000111                       7 1 1---567
AAAnb3AAEAAAEAmAAD 000000000111                 12 1 ---------101112

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Bharat K, June 26, 2008 - 8:08 pm UTC

Before commenting on the response , I liked the way we had an interaction in scrutinizing the requirement. I also appreciate your answer and the efforts you have put in understanding the requirement and providing me the soultion.

However I have to understand the usage of cast and multiset functions becuase I have'nt used them anytime before. I shall take sometime to understand the solution but iam happy to see a solution for this problem. Thanks a lot

how about this ?

amit poddar, June 30, 2008 - 9:58 pm UTC

select x, (select
                replace(replace( xmlagg(xmlelement("a",decode(substr(x,level,1),'0','-','1',to_char(level)))
                                     order by level
                                 ),'<a>',''
                               ),'</a>',''
                       )
                        from dual
                       connect by level <= length(x)
          ) column_value
from t

Will the above work ?

July 06, 2008 - 7:05 pm UTC

sure, it basically strings things up:

000000000111
<a>-</a><a>-</a><a>-</a><a>-</a><a>-</a><a>-</a><a>-</a><a>-</a><a>-</a><a>10</
a><a>11</a><a>12</a>

and since we are dealing with numbers here - nothing special, no special characters - xml would "work"

Help required to obtain output for variable string length

Tim, July 02, 2008 - 3:52 pm UTC

Tom,

Could you please explain the query. It is very confusing with lot of new keywords...

Thanks,
Tim

July 07, 2008 - 9:05 am UTC

Ok, I can try....

I usually break things down into bits and pieces to understand myself so...

ops$tkyte%ORA11GR1> select level
  2    from dual
  3  connect by level <= 5;

     LEVEL
----------
         1
         2
         3
         4
         5

<b>we can use that technique in 9ir2 and above to generate a "set", you can generate as many rows as you need that way....</b>

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select *
  2    from table ( sys.odcivarchar2list( 'a', 'b', 'c' ) )
  3  /

COLUMN_VALUE
------------------------------
a
b
c

<b>we can use the fact that there is a type defined for us in 10g that is a varchar2 type - a table of varchar2's.  You could also:

create type myArray as table of varchar2(4000)
/

and use myArray instead of the sys.odcivarchar2list type.

And we use the fact that you can select from a collection using the TABLE function there</b>

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select *
  2    from table( cast( multiset( select level from dual connect by level <= 5 ) as sys.odcivarchar2list ))
  3  /

COLUMN_VALUE
------------------------------
1
2
3
4
5

<b>multiset has been around since 8.0 with the new collection types - we can take a SET and using MULTI-SET turn it into a collection.  We want the collection type to be sys.odcivarchar2list, so we CAST the multiset to that type</b>

ops$tkyte%ORA11GR1> select x, column_value
  2    from t, table ( sys.odcivarchar2list( t.rowid, t.x ) )
  3  /

X                    COLUMN_VALUE
-------------------- ------------------------------
11111                AAATpPAAEAAAHdoAAA
11111                11111
101011               AAATpPAAEAAAHdoAAB
101011               101011
1000111              AAATpPAAEAAAHdoAAC
1000111              1000111
000000000111         AAATpPAAEAAAHdoAAD
000000000111         000000000111

8 rows selected.

<b>Now, we use the fact that you can use collection UN-NESTING to perform a self join of a nested table with it's source table - here I just passed the attributes rowid and x to the TABLE() function and we get two rows out for every single row in the table - each row was joined to the output of the TABLE clause</b>

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select rowid rid, x, to_number( substr(column_value,1,4)) lvl, substr( column_value,6,1) bit
  2    from t,
  3         table(
  4          cast(
  5           multiset( select to_char(level,'fm0000')|| '-' || substr(x,level,1)
  6                       from dual
  7                    connect by level <= length(x) ) as sys.odcivarchar2list ) )
  8  /

RID                X                           LVL B
------------------ -------------------- ---------- -
AAATpPAAEAAAHdoAAA 11111                         1 1
AAATpPAAEAAAHdoAAA 11111                         2 1
AAATpPAAEAAAHdoAAA 11111                         3 1
AAATpPAAEAAAHdoAAA 11111                         4 1
AAATpPAAEAAAHdoAAA 11111                         5 1
AAATpPAAEAAAHdoAAB 101011                        1 1
AAATpPAAEAAAHdoAAB 101011                        2 0
AAATpPAAEAAAHdoAAB 101011                        3 1
AAATpPAAEAAAHdoAAB 101011                        4 0
AAATpPAAEAAAHdoAAB 101011                        5 1
AAATpPAAEAAAHdoAAB 101011                        6 1
AAATpPAAEAAAHdoAAC 1000111                       1 1
AAATpPAAEAAAHdoAAC 1000111                       2 0
AAATpPAAEAAAHdoAAC 1000111                       3 0
AAATpPAAEAAAHdoAAC 1000111                       4 0
AAATpPAAEAAAHdoAAC 1000111                       5 1
AAATpPAAEAAAHdoAAC 1000111                       6 1
AAATpPAAEAAAHdoAAC 1000111                       7 1
AAATpPAAEAAAHdoAAD 000000000111                  1 0
AAATpPAAEAAAHdoAAD 000000000111                  2 0
AAATpPAAEAAAHdoAAD 000000000111                  3 0
AAATpPAAEAAAHdoAAD 000000000111                  4 0
AAATpPAAEAAAHdoAAD 000000000111                  5 0
AAATpPAAEAAAHdoAAD 000000000111                  6 0
AAATpPAAEAAAHdoAAD 000000000111                  7 0
AAATpPAAEAAAHdoAAD 000000000111                  8 0
AAATpPAAEAAAHdoAAD 000000000111                  9 0
AAATpPAAEAAAHdoAAD 000000000111                 10 1
AAATpPAAEAAAHdoAAD 000000000111                 11 1
AAATpPAAEAAAHdoAAD 000000000111                 12 1

30 rows selected.

<b>Now I've just expanded the data out - so that the bit string is running DOWN the page instead of across the page and LVL is assigned, so we can discern bit 12 from bit 11 and so on..</b>

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> with data
  2  as
  3  (
  4  select rowid rid, x, to_number( substr(column_value,1,4)) lvl, substr( column_value,6,1) bit
  5    from t,
  6         table(
  7          cast(
  8           multiset( select to_char(level,'fm0000')|| '-' || substr(x,level,1)
  9                       from dual
 10                    connect by level <= length(x) ) as sys.odcivarchar2list ) )
 11  )
 12  select data.* , replace( sys_connect_by_path( decode( bit, '1', to_char(lvl), '-' ), ' ' ), ' ', '') scbp
 13    from data
 14  where lvl = length(x)
 15  start with lvl = 1
 16  connect by prior rid = rid and prior lvl+1 = lvl
 17  /

<b>if we start with lvl = 1 and connect by rowid and lvl - we can reassemble the string using sys_connect_by_path and turn '1's into the lvl and everything else into a dash - then we just keep the one row we are interested in (the last one in the hierarchy)</b>


RID                X                           LVL B SCBP
------------------ -------------------- ---------- - ------------------------------
AAATpPAAEAAAHdoAAA 11111                         5 1 12345
AAATpPAAEAAAHdoAAB 101011                        6 1 1-3-56
AAATpPAAEAAAHdoAAC 1000111                       7 1 1---567
AAATpPAAEAAAHdoAAD 000000000111                 12 1 ---------101112

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer