Format SQL fails when using TRIM() function

tentrade

I have the following line in my T-SQL

SELECT @sql=TRIM(',' FROM @sql)

When this line is present the 'Format SQL' fails with Exception "Cannot align to a future token "," at line 73, column 21, offset 2919". Column 26,27 and 28 are ',' in the select statement.

If I comment out this line, then the 'Format SQL' completes successfully.

Russell D

What version of Prompt are you using, and can you share the full script please? I can't find any problems with Trim().

tentrade1

I am using the most recent version, 9.5.12.10845

Here is the full script (Format SQL works if the line with TRIM() in it is commented out)

USE [database1]

GO

/****** Object:  StoredProcedure [dataimport].[FileToTable]    Script Date: 8/6/2019 4:16:58 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dataimport].[FileToTable]

@filepathandname [nvarchar](255),

@DBName [nvarchar](128),

@TableSchema [nvarchar](128),

@TableName [nvarchar](128),

@addExtraColumns [bit] = 1

WITH EXECUTE AS CALLER

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

        IF @filepathandname LIKE '"%"'

            SET @filepathandname = SUBSTRING(@filepathandname, 2, LEN(@filepathandname) - 2);

        DECLARE @FILENAME NVARCHAR(255) = REVERSE(LEFT(REVERSE(@filepathandname), CHARINDEX('\', REVERSE(@filepathandname)) - 1));

        DECLARE @PATH NVARCHAR(255) = LEFT(@filepathandname, PATINDEX('%' + @FILENAME, @filepathandname) - 1);

        DECLARE @sql VARCHAR(MAX);

        DECLARE @dbschematable1 NVARCHAR(255) = N'[' + @DBName + N'].[' + @TableSchema + N'].[' + @TableName + N'1]';

        DECLARE @dbschematable2 NVARCHAR(255) = N'[' + @DBName + N'].[' + @TableSchema + N'].[' + @TableName + N']';

        --1. check the file, get column names, create table with these column names

        EXEC ('DROP TABLE IF EXISTS ' + @dbschematable1 + ';');

        SET @sql = 'SELECT TOP 0 * INTO ' + @dbschematable1 + '

FROM OPENROWSET(''Microsoft.ACE.OLEDB.16.0'',

''Text;Database='  + @PATH + ';HDR=YES;MaxScanRows=1;IMEX=1'',

''SELECT TOP 1 * FROM ' + @FILENAME + ''')';

        EXEC (@sql);

        DROP TABLE IF EXISTS #colnames;

        CREATE TABLE #colnames (id INT, colname NVARCHAR(255));

        EXEC ('INSERT #colnames SELECT column_id,name FROM ' + @DBName + '.sys.columns WHERE object_id = object_id(''' + @dbschematable1 + ''');');

        SET @sql = 'CREATE TABLE ' + @dbschematable1 + '(';

        DECLARE @colname VARCHAR(MAX);

        DECLARE @colname2 VARCHAR(MAX);

        SELECT @colname = MIN(colname)

        FROM   #colnames

        WHERE  id = (SELECT MIN(id)FROM #colnames);

        WHILE @colname IS NOT NULL

            BEGIN

                SET @colname2 = @colname;

                IF @colname IN (SELECT currentname FROM #RenameThese)

                    SELECT @colname2 = newname

                    FROM   #RenameThese

                    WHERE  currentname = @colname;

                SET @sql = @sql + '[' + @colname2 + '] [NVARCHAR](MAX) NULL,';

                SELECT @colname = MIN(colname)

                FROM   #colnames

                WHERE  id = (SELECT MIN(id)FROM #colnames WHERE id > (SELECT c.id FROM #colnames c WHERE c.colname = @colname));

            END;

        DROP TABLE IF EXISTS #colnames;

        SELECT @sql=TRIM(',' FROM @sql)+') ON [PRIMARY]';

        EXEC ('DROP TABLE IF EXISTS ' + @dbschematable1 + ';');

        EXEC (@sql);

        --now do the data insert

        EXEC ('BULK INSERT ' + @dbschematable1 + '

FROM ''' + @filepathandname + '''

WITH

(

FORMAT = ''CSV'', 

FIELDQUOTE = ''"'',

FIRSTROW = 2,

FIELDTERMINATOR = '','',  --CSV field delimiter

ROWTERMINATOR = ''\n'',   --Use to shift the control to next row

TABLOCK

)'  );

        IF @addExtraColumns = 1

            BEGIN

                DECLARE @columnlisttable TABLE (collist VARCHAR(MAX));

                INSERT @columnlisttable (collist)

                EXEC ('SELECT a.NAME FROM '+@DBName+'.sys.columns A WHERE a.object_id = OBJECT_ID(''' + @dbschematable1 + ''')');

SELECT collist FROM @columnlisttable

                SET @sql = 'DROP TABLE IF EXISTS ' + @dbschematable2 + ';

                SELECT *

                      '    + IIF('FullName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(2440), NULL) AS FullName', '') + '

                      '    + IIF('LastName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS LastName', '') + '

                      '    + IIF('FirstName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS FirstName', '') + '

                      '    + IIF('MiddleOtherNames' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS MiddleOtherNames', '') + '

                      '    + IIF('SuffixGen' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(70), NULL) AS SuffixGen', '') + '

                      '    + IIF('DOBDT' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(DATE, NULL) AS DOBDT', '') + '

'                          + IIF('DispositionDateDT' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(DATE, NULL) AS DispositionDateDT', '') + '

                INTO   '   + @dbschematable2 + '

                FROM   '   + @dbschematable1 + '

DROP TABLE IF EXISTS '     + @dbschematable1;

                EXEC (@sql);

            END;

        ELSE

            BEGIN

                EXEC ('DROP TABLE IF EXISTS ' + @dbschematable2 + ';

                SELECT *

                INTO   ' + @dbschematable2 + '

                FROM   ' + @dbschematable1 + '

DROP TABLE IF EXISTS ' + @dbschematable1);

            END;

    END;

GO

tentrade

Nudge. Any news on this?

Russell D

Using 9.5.15 this formats without problem for me - whilst I can't actually find that we've fixed anything in particular to do with TRIM, I couldn't reproduce it before either. Can you update and try again?

PaulShearing

I too have this problem. The TSQL TRIM function can take an additional parameter which is the string sequence (usually a single character) to be removed from the start and end of a string. The default value is the single space character, as in: TRIM(@stringVar) which works fine but TRIM(',', @stringVar) to remove leading or trailing commas causes SqlPrompt to display an error panel.  My little string tidy function is:

<div><i>create function [Maintenance].[strTidy]</i></div><div><i>(</i></div><div><i>&nbsp; &nbsp; @str NVARCHAR(max)</i></div><div><i>)</i></div><div><i>returns NVARCHAR(max)</i></div><div><i>as</i></div><div><i>&nbsp; &nbsp; begin</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; declare @ResultVar NVARCHAR(max);</i></div><div><br></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = TRIM( COALESCE( @str, '' ));</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = TRIM( ',' from @ResultVar);&nbsp; <b><<-- Comment this line and SQL Prompt is OK</b></i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = REPLACE( @ResultVar, '&nbsp; ', ' ' );</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; if @ResultVar = 'undisclosed'</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = N'';</i></div><div><br></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; return @ResultVar;</i></div><div><br></div><div><i>&nbsp; &nbsp; end</i></div>

SQL Prompt VS 9.5.16.11294
SQL Server: 14.0.2027.2

Russell D

Ok that one helps reproduce it, thank you. I've raised this as SP-7802 and will come back to you when we've had a chance to review it.

Russell D

This is fixed in 9.5.18 - https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_9.5.18.11513.exe. Let me know how you get on.

ccparkhill

Still not completely fixed, the below SQL now formats fine:

<div>SELECT TRIM('AW' FROM [dc].[CustomerAlternateKey]) AS [CustomerAlternateKey]&nbsp; , [dc].[CustomerKey] FROM [dbo].[DimCustomer] AS [dc];</div>

However, when TRIM is not in the first column "Format SQL" fails with "Exception: Cannot align to a future token "AW" at line 2, column 10, offset 37", e.g.:

<div>SELECT [dc].[CustomerKey], TRIM('AW' FROM [dc].[CustomerAlternateKey]) AS [CustomerAlternateKey] FROM [dbo].[DimCustomer] AS [dc];</div>

I'm using 9.5.19.11631.

ccparkhill

Still not working correctly in 9.5.23.

ccparkhill

Still not working correctly in 10.0.1.12389.