TRIM Syntax using LEADING for compatibility_level 2022 160 will not compile SSMS or VS2022

Question

TRIM Syntax using LEADING for compatibility_level 2022 160 will not compile SSMS or VS2022 database project.

E.g. in SSMS
SELECT TRIM(LEADING '*' FROM '*1234565')
Errors
Invalid column name 'Leading'.
Incorrect syntax near "*1234565".

E.g. in Visual Studio 2022 (and I tried Visual Studio 2022 Preview)

These issues particularly the VS2022 one should go away as the database compatability is 160 and the code runs fine.. i have latest version of SSMS and latest version of Visual Studio etc.

Also, as the visual studio compiler error is stopping me from being able to sync my production database with the database project.

Any help getting the compiler to accept this code (which is valid syntax, as per here ) would be apprecaited

Regards
Eddy
p.s. i remove "LEADING" in this and the problem goes away but really the word "LEADING" should be accepted by the VS2022 compiler as it is valid syntax for non Azure Databases?

Answer 1

Yes - SSMS can run the query containing 'LEADING' despite the red underline as per the screenshot. SSMS is not really stopping me from doing anything, but it is quite strange that is being underlined as it is perfectly valid.

The main thing to note is that you will need your database compatibility level set to 160 to use the LEADING, TRAILING, or BOTH keywords. If you do not, then you can still use TRIM, just not with those specific keywords. But those keywords are being underlined even when using a database set to 160 and or when configuring VS2022 to use 2022 (which is 160) as described here.

p.s. I can get away with not using those keywords in my project fortunately, because simply using TRIM (and my use case involves a string where the character i want to remove only ever appears at the start of the string) without these keywords behaves like 'BOTH' as below, and so it still does what i need it to do,

but i am sure if there were a use case where someone wanted to just remove it from just the start or end of the string and was using the LEADING or TRAILING syntact then this would show with underline in SSMS and would simply not compile in VS2022 database projects when set to target platform SQL Server 2022 as below.