How to convert a number to a string with the thousands separator?

Question

Is there an equivalent to the FORMAT() function (SQL Server) in Advantage Database Server? For example using T-SQL I can write the following code:

SELECT FORMAT(1000000, '#,0.00');

This will return "1,000,000.00". But ADS does not have the FORMAT() function. I tried the STR() function:

SELECT STR(1000000, 10, 2) FROM system.iota;

However the result is "1000000.00".

Answer 1

I'm not an expert in ADS but I hope this method will help, this is written in SQL using very basic methods you should be able to easily replicate it in ADS

   --DECLARE @Number INT = 123456.89 
DECLARE @Number FLOAT = '123456.89' -- Input number as FLOAT

DECLARE @StringOutput VARCHAR(55) ='' -- Output string for formatted number

-- Load the Table with the individual Characters
CREATE TABLE #CharacterTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SINGLECHAR CHAR(1)
)

DECLARE @cNumber NVARCHAR(255); -- Temporary variable to store converted number as string
DECLARE @Length INT; -- Length of the input number string
DECLARE @Index INT; -- Index used in looping through characters
DECLARE @Character CHAR(1); -- Individual character extracted from the input number
print @Number

-- Convert the input number to a string
SET @cNumber = CAST(CAST(@Number AS DECIMAL(38,2)) as VARCHAR(41)) 

print @cNumber
SET @Length = LEN(@cNumber);
SET @Index = 1;

-- Loop through the input string and insert individual characters into the table
WHILE @Index <= @Length
BEGIN
    SET @Character = SUBSTRING(@cNumber, @Index, 1);
    INSERT INTO #CharacterTable (SINGLECHAR) VALUES (@Character);
    SET @Index = @Index + 1;
END

-- Find if there is a decimal place
DECLARE @MYID INT
DECLARE @MYIDMin INT
SET @MYID = (SELECT MAX(ID) FROM #CharacterTable)
SET @MYIDMin = (SELECT MIN(ID) FROM #CharacterTable)

DECLARE @DOTID INT

-- Check if a decimal point exists
IF EXISTS (SELECT ID FROM #CharacterTable WHERE SINGLECHAR = '.')
BEGIN
    SET @DOTID = (SELECT ID FROM #CharacterTable WHERE SINGLECHAR = '.')
    WHILE (@DOTID <= @MYID) 
    BEGIN
        SET @Character = (SELECT SINGLECHAR FROM #CharacterTable WHERE ID=@MYID)
        SET @StringOutput = @Character + @StringOutput
        SET @MYID = @MYID - 1
    END
END
ELSE
BEGIN
    SET @StringOutput = '.00' -- If no decimal point found, append '.00' to the output string
END

DECLARE @Count INT = 0;

-- Attach the Decimal part and add comma separators
WHILE EXISTS (SELECT 1 FROM #CharacterTable WHERE ID=@MYID)
BEGIN
    SET @Character = (SELECT SINGLECHAR FROM #CharacterTable WHERE ID=@MYID)
    SET @StringOutput = @Character + @StringOutput
    SET @Count = @Count + 1    
    IF (@Count = 3 AND @MYID <> @MYIDMin)
    BEGIN
        SET @StringOutput = ',' + @StringOutput -- Add comma as a thousand separator every 3 digits
        SET @Count = 0
    END
    SET @MYID = @MYID - 1
END

Select @StringOutput -- Output the formatted number

DROP TABLE #CharacterTable -- Drop the temporary table after use