How to replaces 1 with corresponding position

[lenygold via DBMonster.com]

Hi everybody.

Input table and requested result:
xstring result
----------- --------
11111 12345
101011 1-3-56
1000111 1---567
Any suggestions how to write a query/UDF for replacing 1 with
corresponding position

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200808/1

[4.spam@mail.ru]

Re: How to replaces 1 with corresponding position

Hi everybody.

>
Input table and requested result:
xstring           result
-----------        --------
11111            12345
101011          1-3-56
1000111        1---567
Any suggestions how to write a query/UDF for replacing 1 with
corresponding position

Hello.

Try this:

with xstring(s) as (values
('11111')
, ('101011')
, ('1000111')
)
, t(s, rn, res) as
(
select s, 1, cast(case substr(s, 1, 1) when '1' then '1' else '-' end
as varchar(10))
from xstring
union all
select s, rn+1, res||case substr(s, rn+1, 1) when '1' then
rtrim(char(rn+1 )) else '-' end
from t
where rn<length(s)
)
select t.s, t.res
from t t
join (select s, max(rn) rn from t group by s) g on t.s=g.s and
t.rn=g.rn

Sincerely,
Mark B.

[--CELKO--]

Re: How to replaces 1 with corresponding position

CREATE TABLE InputStrings
(pattern CHAR(5) NOT NULL PRIMARY KEY,
str CHAR(5) NOT NULL );

INSERT INTO InputStrings (pattern, str)
VALUES ('11111', 'abcde'),
('11011', 'abcde'),
('11001', 'abcde'),
('00000', 'abcde'),
('01010', 'abcde'),
('10101', 'abcde');

SELECT I.pattern, I.str,
(CASE WHEN SUBSTR(I.patter n, 1, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 1, 1) END
|| CASE WHEN SUBSTR(I.patter n, 2, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 2, 1) END
|| CASE WHEN SUBSTR(I.patter n, 3, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 3, 1) END
|| CASE WHEN SUBSTR(I.patter n, 4, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 4, 1) END
|| CASE WHEN SUBSTR(I.patter n, 5, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 5, 1) END ) AS result_string
FROM InputStrings AS I

[lenygold via DBMonster.com]

Re: How to replaces 1 with corresponding position

Thank you very much.
It is working perfect.

4.spam@mail.ru wrote:

>Hi everybody.
>>

>[quoted text clipped - 6 lines]

>Any suggestions how to write a query/UDF for replacing 1 with
>correspondin g position

>
>Hello.
>
>Try this:
>
>with xstring(s) as (values
('11111')
>, ('101011')
>, ('1000111')
>)
>, t(s, rn, res) as
>(
>select s, 1, cast(case substr(s, 1, 1) when '1' then '1' else '-' end
>as varchar(10))
>from xstring
union all
>select s, rn+1, res||case substr(s, rn+1, 1) when '1' then
>rtrim(char(rn+ 1)) else '-' end
>from t
>where rn<length(s)
>)
>select t.s, t.res
>from t t
>join (select s, max(rn) rn from t group by s) g on t.s=g.s and
>t.rn=g.rn
>
>Sincerely,
>Mark B.

--
Message posted via http://www.dbmonster.com