VBA equivalent User Defined String formats in TSQL?

Question

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

`FORMAT`, in T-SQL, is for formatting a non-string type value to a specific string representation. For example formatting a date to something like `dd/MM/yyyy` or a numerical value to value leading zero's and thousand separators like `#,000`. It doesn't format string type data types. *Really*, however, any formatting should be done in the presentation layer; it's not the role of the RDBMS. — Thom A, Oct 12 '21 at 13:56
If you really want to do it, you will have to import a CLR assembly to your server that does either https://stackoverflow.com/a/2187770/11683 or https://stackoverflow.com/a/4072859/11683. — GSerg, Oct 12 '21 at 13:58
Are you asking how to replicate the *specific* `"!@@-@-@@@@"` format? — Alex K., Oct 12 '21 at 15:12
@Larnu thank you, and yes I see your point on the role of the RDBMS; however, for what I am trying to accomplish handling it this way would not affect anything outside of the query I am trying to run; I'm only going to use this a few times. — Samuel V, Oct 12 '21 at 18:24

score 0 · Accepted Answer · answered Oct 12 '21 at 19:01

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)

Thank you, Alex. This is a helpful solution that will be very convenient when needing to create lookups in a pinch from SQL Server instead of Access. — Samuel V, Oct 12 '21 at 19:17

VBA equivalent User Defined String formats in TSQL?

1 Answers1