Search Inside an ASCII File from T-SQL
  • First validate the file exists.
  • Then look for the text within.
  • /* SearchFile.sql:  
          Search within a file on the HardDrive and return a record 
          set of the lines found within the file based on the search criteria.
          2007-2008, Karland International (
          Permission to use, copy, modify, distribute and sell this software and its 
          documentation for any purpose is hereby granted without fee, provided that the 
          above copyright notice appears in all copies and that both that copyright notice 
          and this permission notice appear in supporting documentation. Karland International 
          makes no representations about the suitability of this software for any purpose. 
          It is provided "as is" without express or implied warranty.
    DECLARE @Error INT, @i INT
    -- I'm creating a temp array, NOT a Temp table. This is in memory vs HD.
    -- The reason I'm creating this table is if you dont you see the records sets 
    -- each time you call anything. I'm wanting to mask that and only display what
    -- I want shown.
    DECLARE @dbData TABLE (i INT IDENTITY(1,1) NOT NULL, data VARCHAR(7000))
    -- Check to see if file exists
    EXEC master..xp_fileexist 'C:\WINDOWS\win.ini', @i out
    IF @i=1 BEGIN -- 1 = file exists, 0 = file does not exist
         -- Find text inside of file..
         INSERT INTO @dbData EXEC @Error = master..xp_cmdshell 'c:\windows\system32\find.exe /N /I "mail" C:\WINDOWS\win.ini'
         -- 0 = "mail" found in win.ini, 1 = "mail" was not found in win.ini
         IF @Error=0 BEGIN
              -- SQL always puts the first and last line as a null, then on the 
              -- second line puts the "----" trailing with the file name "win.ini"
              DELETE FROM @dbData WHERE data IS NULL OR data LIKE '----------%'
              -- Since this is a single string, above the /N means to give me Line numbers of where it was found.
              -- I'll split that up from the actual text so to show two columns, 'Line #', 'Text'.
              SELECT SUBSTRING(data, 2, CHARINDEX(']', data)-2), SUBSTRING(data, CHARINDEX(']', data)+1, LEN(data)) FROM @dbData
         END ELSE
              SELECT 'Error Code', @Error -- Should always be 1. Codes are only 0 or 1