Programmatically Check Dynamic File Name For Invalid Characters I have a SQL Script that runs every night and will generate files showing each Sales Reps production for the previous day.  This works 99% of the time, the 1% it does not work is when one of the variables has invalid characters, which of course would generate an invalid file name.  Is there a t-sql way of checking if @filename is a valid string and if it is not replace @filename with a generic naming convention so the procedure does not error?

Sample syntax below:

`Declare @RepName NVARCHAR(500), @filename nvarchar(4000), @Detailedinfo varchar(50)

DECLARE cursor1 CURSOR FOR

  Select Distint repname, saleinfo from dailysalesjournal;

  OPEN cursor1

  FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo

  WHILE @@FETCH_STATUS = 0
  BEGIN

    Set @filename = 'C:\Test\GeneratedFromSQLProc\'+@RepName+'\SaleInfo'+@Detailedinfo+'.csv'

    Print @filename

FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo

END

CLOSE cursor1

DEALLOCATE cursor1  `
Just run it through a series of REPLACE statements to get rid of any invalid characters:

SET @filename = REPLACE(@filename, '\','');
SET @filename = REPLACE(@filename, '/','');
SET @filename = REPLACE(@filename, ':','');
SET @filename = REPLACE(@filename, '"','');
SET @filename = REPLACE(@filename, '*','');
SET @filename = REPLACE(@filename, '?','');
SET @filename = REPLACE(@filename, '&lt;','');
SET @filename = REPLACE(@filename, '&gt;','');
SET @filename = REPLACE(@filename, '|','');


If it doesn't contain the character, it'll leave it unchanged. If it finds it, it'll remove it.