Format SQL fails when using TRIM() function

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

  Russell D August 05, 2019 07:48

  - 0 +
• 
  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

  
  

  
  

  tentrade1 August 06, 2019 19:41

  - 0 +
• 
  Nudge. Any news on this?

  tentrade August 13, 2019 15:58

  - 0 +
• 
  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?

  Russell D September 06, 2019 10:46

  - 0 +
• 
  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

  PaulShearing September 07, 2019 09:13

  - 0 +
• 
  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 September 12, 2019 12:56

  - 0 +
• 
  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.

  Russell D September 24, 2019 07:38

  - 0 +
• 
  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 September 25, 2019 14:52

  - 0 +
• 
  Still not working correctly in 9.5.23.

  ccparkhill October 30, 2019 10:29

  - 0 +
• 
  Still not working correctly in 10.0.1.12389.
  

  ccparkhill November 20, 2019 09:07

  - 0 +

Add comment

Please sign in to leave a comment.