Replace portion of string 1

[DugsDMan]

I'm trying to help a friend at work figure this one out. Here's the situation. He has the table below:

Type     Value
A          1
A          4
A          6
B          2
B          3
B          7

What we need to do is query the table and create one output record for each Type. However, the output record needs to contain Type and a bit string set by looking at Value.

Output Ex:
A1001010 (bit string is all 0, except for pos 1, 4, & 6)
B0110001 (bit string is all 0, except for pos 2, 3, & 7)

This file is getting passed to a mainframe program that is expecting the output in the format above. The actual output variable will have over 200 places in it.

I can't think of a way to do this with just plain Ole SQL. Is there a function or something that can be used to replace a single character in a string? I know in VB, ColdFusion, etc. there are functions that will allow one to change all/part of a string. If there is, I think we should be able to partition by Type and replace each character at the Value position with a 1.

As always, your help is appreciated!!!

Thanks,
Doug

 

[lewisp]

You could create a function such as this:

CREATE FUNCTION val_to_bin (p_type IN VARCHAR2)
RETURN VARCHAR2 IS
  l_str VARCHAR2(255) := '00000000';
  l_val NUMBER;
--
  CURSOR cur_val IS
    SELECT value
    FROM   table
    WHERE  type = p_type
    ORDER BY value;
BEGIN
  FOR c IN cur_val
  LOOP
    l_str := substr(l_str,1,c.value - 1) || '1' || substr(l_str,c.value + 1);
  END LOOP;
--
  RETURN l_str;
END;

Then use it in SQL like this:

SELECT type || val_to_bin(type)
FROM  table;

 

[DugsDMan]

Thanks. I created the function and ran a quick test. After I added a distinct to the SQL, it comes out just like we need it.

Thanks for such a quick and good answer!!!

'Tis a star for you!