Add Zero to certain specific places in column value

Question

The input table looks like:

declare @mytable table
(
REG INT,
ISTR INT,
DAT DATETIME,
TRN INT

)
INSERT @mytable

SELECT 8,   1,      '2023-04-23 00:00:00',  776862 UNION ALL
SELECT 03,  238,    '2023-04-23 00:00:00',  300139 UNION ALL
SELECT 3,   238,    '2023-04-23 00:00:00',  300139 UNION ALL
SELECT 12,  172,    '2023-04-23 00:00:00',  237257 UNION ALL
SELECT 6,   1,      '2023-04-23 00:00:00',  849848

SELECT * FROM @mytable

I am trying to generate 2 output table (one called as Original and other as Duplicate)

Rule:

Duplicate -->PadLeft(REG,'0',3) + PadLeft(ISTR,'0',5) + FormatDateTime(DAT, 'YYYYMMDDHHmmss' ) + PadLeft(TRN,'0',10)

Original --> Padleft 0 ,3 (REG (3 places) + STR (5 places) + DAT (14 places) + TRN (10 places)

The Output table contains data with below mention rule.

The Value For REG Should be always start from 3rd place, if not preceed with 0 to make it from Third Place.

The Value for ISTR always start from 5th Place, if not then preceed with 0 to make it at 5th place.

The Value for DAT always start from 14th Place, if not then preceed with 0 to make it at 14th place.

The Value for TRN always start from 10th Place, if not then preceed with 0 to make it at 10th place.

Can we do this with SQL?

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 25 '23 at 09:55
to pad stuff from the left you do: select RIGHT(replicate('0', padlength) + cast(somevalue as nvarchar(1000)),padlength) . — siggemannen, Apr 25 '23 at 10:03
to format date, use: select format(yourdate, 'yyyyMMddHHmmss') — siggemannen, Apr 25 '23 at 10:05
Or, probably better than `FORMAT`, use `CONVERT` and a style code. — Thom A, Apr 25 '23 at 10:15
@siggemannen: The date appear like. "3 2023 12:00AM", but what i am trying the date looks like Int excluding AM OR PM, so later I can concatenate all values. Please help me. — Rajee Kasthuri, Apr 25 '23 at 10:38
well, if you cast the date to nvarchar, that's what happens. You have to use the FORMAT thing i wrote to get yyymmm... value — siggemannen, Apr 25 '23 at 10:40
@siggemannen: When I tried to use format, getting error as " Argument data type nvarchar is invalid for argument 1 of format function...... any other way ? Please help. — Rajee Kasthuri, Apr 25 '23 at 11:40

score 0 · Answer 1 · answered Apr 25 '23 at 17:43

I'm not really sure what you mean by output 2 tables, it would help if you can show your intended output as well.

Saying that, you could easily follow on what Thom and siggemannen suggested already... if not, see if the below produces what you're expecting.

DECLARE @mytable TABLE (
    REG INT,
    ISTR INT,
    DAT DATETIME,
    TRN INT
)
INSERT INTO @mytable (
    REG, ISTR, DAT, TRN
)
VALUES
    (8,   1,      '2023-04-23 00:00:00',  776862),
    (03,  238,    '2023-04-23 00:00:00',  300139),
    (3,   238,    '2023-04-23 00:00:00',  300139),
    (12,  172,    '2023-04-23 00:00:00',  237257),
    (6,   1,      '2023-04-23 00:00:00',  849848)

SELECT
    *,
    Result =
        REPLICATE(N'0', 3 - LEN(REG)) + CAST(REG AS NVARCHAR(10)) +
        REPLICATE(N'0', 5 - LEN(ISTR)) + CAST(ISTR AS NVARCHAR(10)) +
        FORMAT(DAT, 'yyyyMMddHHmmss') + 
        REPLICATE(N'0', 10 - LEN(TRN)) + CAST(TRN AS NVARCHAR(10))
FROM
    @mytable

Result:

REG	ISTR	DAT	TRN	Result
8	1	2023-04-23 00:00:00.000	776862	00800001202304230000000000776862
3	238	2023-04-23 00:00:00.000	300139	00300238202304230000000000300139
3	238	2023-04-23 00:00:00.000	300139	00300238202304230000000000300139
12	172	2023-04-23 00:00:00.000	237257	01200172202304230000000000237257
6	1	2023-04-23 00:00:00.000	849848	00600001202304230000000000849848

Add Zero to certain specific places in column value

1 Answers1