By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [test](
[id] [int] NOT NULL,
[id2] [int] NOT NULL,
[col1] [varchar](6) NOT NULL
)
INSERT INTO [test] VALUES
(4080,2147,'00'),
(4080,2148,'0001'),
(4080,2149,'0002'),
(4080,2150,'000201'),
(3820,2069,'00'),
(3820,2070,'000101')
6 rows affected
select a.*, case when a.col1 = '00' then null else coalesce(b.id2, c.id2) end as bid2
from test a
left join test b on b.id = a.id and SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1
left join test c on c.id = a.id and c.col1 = '00'
| id | id2 | col1 | bid2 |
|---|---|---|---|
| 4080 | 2147 | 00 | null |
| 4080 | 2148 | 0001 | 2147 |
| 4080 | 2149 | 0002 | 2147 |
| 4080 | 2150 | 000201 | 2149 |
| 3820 | 2069 | 00 | null |
| 3820 | 2070 | 000101 | 2069 |