Format a column to get a specific number of value

Question

I have some numbers in a column which should be start with 'C' and 7 numbers.

Conditions:
--If there are 8 characters do nothing
--if there are not 8 characters add a 'C' at beginning and complete with '0' between the 'C' and the rest of numbers

CREATE TABLE WrongValue (
    number varchar(8)
);
insert into WrongValue(number) values
('1'),
('12'),
('1234567'),
('1456'),
('456'),
('C4534567'),
('15613');

select * from WrongValue

--If there are 8 characters do nothing
--if there are not 8 characters add a 'C' at beginning and complete with '0' between the 'C' and the rest of numbers 

CREATE TABLE ExpectedValue (
    number varchar(8)
);
insert into ExpectedValue(number) values
('C0000001'),
('C0000012'),
('C1234567'),
('C0001456'),
('C0000456'),
('C4534567'),
('C0015613');

select * from ExpectedValue

db<>fiddle

Answer 1

Maybe this is what you want ?

select w.number,
       case when len(w.number) = 8 then w.number
            else 'C' + replicate('0', 7 - len(w.number)) + w.number
       end as CorrectedNumber,
       -- better method, thanks to Aaron
       'C'+ RIGHT(REPLICATE('0',7) + w.number, 7) as BestCorrection
from   WrongValue w

DBFiddle

Result

number	CorrectedNumber	BestCorrection
1	C0000001	C0000001
12	C0000012	C0000012
1234567	C1234567	C1234567
1456	C0001456	C0001456
456	C0000456	C0000456
C4534567	C4534567	C4534567
15613	C0015613	C0015613

One question remains, can there be values longer than 8 ?
If, so what to do with them ?