VBA equivalent User Defined String formats in TSQL?

VBA allows for user-defined string formats in Format(). I am particularly interested in replicating the placeholder characters, @ and ! in SQL Server (using its Format() function? - open to alternatives).

My use case requires a mix of characters and numbers stored as a Variant type in VBA.

With @ and ! placeholder characters, here is what I would like to mimic from VBA in SQL Server.

VBA: Format(12DFR89, "!@@-@-@@@@")

Output: 12-D-FR89

Solution

As your asking about reproducing the fixed format "!@@-@-@@@@" you can do this a with UDF that replicates the VBA behaviour:

CREATE FUNCTION dbo.CustomFormat(@VALUE VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS 
BEGIN 
    DECLARE @MAX_LENGTH INT = 7

    SET @VALUE = RIGHT(@VALUE + ISNULL(RIGHT(REPLICATE('?', @MAX_LENGTH - LEN(@VALUE)), @MAX_LENGTH), ''), @MAX_LENGTH)

    RETURN CONCAT( 
        LEFT(@VALUE, 2),
        '-',
        SUBSTRING(@VALUE, 3, 1),
        '-',
        SUBSTRING(@VALUE, 4, @MAX_LENGTH)
    )
END
GO

Example:

SELECT
    test,
    dbo.CustomFormat(test)
FROM ( VALUES
    ('1'), ('12'), ('123'), ('1234'), ('12345'), ('123456'), ('1234567'), ('12345678'), ('123456789'), ('1234567890')
) AS T(test)

For:

test
1           1?-?-????
12          12-?-????
123         12-3-????
1234        12-3-4???
12345       12-3-45??
123456      12-3-456?
1234567     12-3-4567
12345678    23-4-5678
123456789   34-5-6789
1234567890  45-6-7890

(Replace '?' with ' ' in the function to get spaces)