我在一列中有一些數字應該以“C”和7個數字開頭。
條件：
--如果有 8 個字符，什么都不做 --如果
沒有 8 個字符，在開頭添加一個“C”，在“C”和其余數字之間添加“0”
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<>小提琴
uj5u.com熱心網友回復：
也許這就是你想要的？
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
結果
| 數字 | 更正編號 | 最佳矯正 |
|---|---|---|
| 1 | C0000001 | C0000001 |
| 12 | C0000012 | C0000012 |
| 1234567 | C1234567 | C1234567 |
| 1456 | C0001456 | C0001456 |
| 456 | C0000456 | C0000456 |
| C4534567 | C4534567 | C4534567 |
| 15613 | C0015613 | C0015613 |
仍然存在一個問題，是否存在超過 8 的值？
如果，那該怎么辦？
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/456563.html
上一篇：SQL-將一列資料轉換為一行