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)


  Select Distint repname, saleinfo from dailysalesjournal;

  OPEN cursor1

  FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo


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

    Print @filename

FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo


CLOSE cursor1

DEALLOCATE cursor1  `
  • I don't think there is a "check for a valid filename" function or anything, you'll probably have to do an explicit REPLACE for invalid characters you know might be there, based on what you know about the source. I think the invalid windows characters are \ / : * ? " < > and | – BradC Jun 5 '17 at 13:39
  • @BradC - I have seen \ / " * ? <> in the filename. You recommend to do a check once I have set the filename, and if it contains any of those characters to just do a replace? – Smith Stanley Jun 5 '17 at 13:41
  • 1
    Yeah, just run it through a series of SET @filename = REPLACE(@filename, '\','') to get rid of any invalid characters. – BradC Jun 5 '17 at 13:45
  • No problem, I posted as an answer for posterity. – BradC Jun 5 '17 at 13:49

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, '<','');
SET @filename = REPLACE(@filename, '>','');
SET @filename = REPLACE(@filename, '|','');

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

| improve this answer | |
  • Would the * be considered a wildcard and replace the entire string? – Smith Stanley Jun 5 '17 at 14:07
  • 1
    @SmithStanley No, * and ? aren't SQL wildcards. SQL wildcards are % and _, and those only work in a LIKE statement. – BradC Jun 5 '17 at 14:13
  • You should add '.' (period) to the list of invalid characters -- the danger is if the filename ends in a period with no extension. I was using sp_OACreate and sp_OAMethod and didn't pay attention to the filenames -- now I have files and folders I cannot delete because Windows cannot find the file. – D. Kermott Mar 24 at 21:10

Adding to BradC's answer, here's another way to replace troublesome characters using a wildcard:

--- These characters are allowed:
DECLARE @allowed varchar(1000)='a-zA-Z0-9 ';

--- Loop through each disallowed character in @filename:
WHILE (@filename COLLATE Latin1_General_BIN LIKE '%[^'+@allowed+']%')
    SET @filename=STUFF(@filename,
                                 @filename COLLATE Latin1_General_BIN), 1, '');

You can set a SQL Server-style wildcard in the @allowed variable to determine which characters are "legal". In my example, I'm allowing lower-case and upper-case A-Z as well as numeric values and space.

| improve this answer | |
  • Very interesting approach. I never thought of setting a variable equal to A-Z and 0-9 then checking the value against that. I have not tested (yet) but I assume will be much quicker than multiple Replace statements. – Smith Stanley Jun 5 '17 at 19:10
  • Actually, it's just a loop, so performance for the multiple REPLACE statements depends on the number of replacements, whereas the performance of this loop pattern depends on the number of iterations, i.e. the number of characters that need to be replaced. For a single variable like this, execution time will be negligible either way. – Daniel Hutmacher Jun 5 '17 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.