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
CodePudding user response:
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)