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....m-db2/200808/1

[4.spam]

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--]

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.pattern, 1, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 1, 1) END
|| CASE WHEN SUBSTR(I.pattern, 2, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 2, 1) END
|| CASE WHEN SUBSTR(I.pattern, 3, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 3, 1) END
|| CASE WHEN SUBSTR(I.pattern, 4, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 4, 1) END
|| CASE WHEN SUBSTR(I.pattern, 5, 1) = '0' THEN '-' ELSE
SUBSTR(I.str, 5, 1) END ) AS result_string
FROM InputStrings AS I

[lenygold via DBMonster.com]

Thank you very much.
It is working perfect.

4.****@mail.ru wrote:

>Hi everybody.

[quoted text clipped - 6 lines]

>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.

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