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

Where did you get stuck? Does this answer your question? [Formatting Numbers by padding with leading zeros in SQL Server](https://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server) — Thom A, Apr 05 '22 at 14:52

GuidoG · Accepted Answer · 2022-04-05T15:06:21.140

2

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 ?

edited Apr 05 '22 at 15:06

answered Apr 05 '22 at 14:57

GuidoG

11,359
6
44
79

1

+1 Not that it would matter in a practical sense but from a theoretical standpoint `'C'+ RIGHT(REPLICATE('0',7) + w.number, 7)` should be slightly more efficient from a computational perspective because you don't strictly _need_ to calculate the existing length. Whether it matters is a different story. :-) – Aaron Bertrand Apr 05 '22 at 15:01
1

@AaronBertrand Good one, I edited my answer with this, thank you – GuidoG Apr 05 '22 at 15:06
in my case there is no number with 8 digit :) hopefully for me ! – KeusT Apr 05 '22 at 22:47

Format a column to get a specific number of value

1 Answers1