TRIM with LEADING/TRAILING/BOTH breaks formatting on Azure SQL Database

orderbynewid

• SQL Prompt version: 10.14.4.4865
• SQL Server Management Studio version: 19.2.56.2
• Database Compatibility Level: 160
• Active Style: Default

Before formatting

sElEcT tRiM(lEaDiNg '0' fRoM '01') AS Description,
    2 AS Period,
    3 AS Precision,
    4 AS Scale,
    5 AS MyColumn

After formatting when connected to Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64)

SELECT TRIM(LEADING '0' FROM '01') AS Description,
    2 AS Period,
    3 AS Precision,
    4 AS Scale,
    5 AS MyColumn;

• This is the expected result

After formatting when connected to Microsoft SQL Azure (RTM) - 12.0.2000.8

SELECT TRIM(lEaDiNg '0' FROM '01') AS DESCRIPTION,
    2 AS PERIOD,
    3 AS PRECISION,
    4 AS Scale,
    5 AS MyColumn;

• lEaDiNg is not cased correctly
• Some column aliases are incorrectly made uppercase

Ben_P

Hi Per,

Thank you for your post.

We've been able to reproduce this locally, so it will be escalated to the development team for a fix. 

Ben_P

Hi Per,

The Dev team have advised that the issue you are seeing is due to using an incorrect syntax for TRIM. The syntax for SQL Server 2022 (16.x) and earlier versions, Azure SQL Database, and Azure Synapse Analytics:

TRIM ( [ characters FROM ] string )

From https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=azuresqldb-current

the given example there is:

SELECT TRIM( '.,! ' FROM ' # test .') AS Result;

The syntax for SQL Server 2022 (16.x) and later versions, Azure SQL Managed Instance, and Microsoft Fabric:

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

 

 

orderbynewid

I have the database compatibility level set to 160, so the syntax with leading/trailing/both is correct, but the formatting is incorrect.