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

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

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

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

orderbynewid December 09, 2023 00:17

3 comments

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 January 09, 2024 15:09

0
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 )
```
Ben_P February 07, 2024 12:33

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

orderbynewid February 07, 2024 14:08

0

Please sign in to leave a comment.

How can we help you today?