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

CodePudding user response：

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

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