Search code examples
windowsbatch-filesubstring

Check for text variable substring in environment variable

While trying to create a batch file that runs a SQL script by calling out to sqlcmd.exe, I'm trying to get the batch file to detect for the presence of the exe and add it to %PATH% automatically before executing. I can guarantee that it will be somewhere on the host but not where.

Looking at the many examples on SO, this suggests that the most robust way of detecting if string A is in string B is to echo out and pipe to findstr to account for case insensitivity, etc.

With this in mind, I have the following in my batch file;

@echo off

REM Ensure we can find the sql interpreter
echo Checking for SqlCmd.exe, please wait...
setlocal EnableDelayedExpansion
for /f "delims=" %%F in ('dir "%programfiles%\sqlcmd.exe" /s/b') do (
    set filepath=%%~dpF
    echo "Found SQL interpreter in: !filepath!"

    REM is it part of the PATH already?
    echo %path% | findstr /c:"!filepath!" 1>nul
    if errorlevel 1 (
        echo "SqlCmd is not part of the current ENVAR PATH, setting..."
        set path=%path%;!filepath!
    )
)

And has the following output;

Checking for SqlCmd.exe, please wait...
\Microsoft was unexpected at this time.

Despite the text Found SQL interpreter in: not appearing in the output, the actual issue seems to be in the next executable line (tested by removing it and running):

echo %path% | findstr /c:"!filepath!" 1>nul

It seems to be echoing out the whole line and executing rather than echoing out the %path% and piping to findstr. I've tried adding quotes to no effect.

I'm almost there but missing an encoding step/tweak to get this to work.

Update 1 The PATH variable contents on my test machine is;

C:\Program Files\Common Files\Oracle\Java\javapath;C:\Program Files\Microsoft MPI\Bin\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\Program Files (x86)\Microsoft SQL Server\150\Tools\Binn\;C:\Program Files\Microsoft SQL Server\150\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\;C:\Program Files\Microsoft SQL Server\150\DTS\Binn\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\;C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;C:\Program Files\dotnet\;C:\ProgramData\chocolatey\bin;C:\Program Files (x86)\Windows Kits\10\Windows Performance Toolkit\;C:\Program Files\Git\cmd;C:\Users\User\.dotnet\tools;C:\Program Files\Java\jdk-17.0.3.1\bin;C:\Users\User\AppData\Local\Programs\Microsoft VS Code\bin;C:\Program Files\Azure Data Studio\bin
Solution

  • After very helpful suggestions from Magoo and Mofi and some experimentation, I tweaked the logic such that it was

    • Correctly accounting for case (I had ommitted the /I)
    • Fixed the sytax issue of /s/b to /s /b
    • Added the new setlocal EnableExtensions flag
    • Changing the loop variable to J

    I had also applied the other suggestions but they had no defacto effect (valid subtlties and distinctions aside) on the running of the batch file. I then hit the hurdle of;

    set path="%path;%%~dpF"

    This resulted in %PATH% having a leading and trailing " which broke all references to anything %PATH% was referencing. Leaving out the " meant that the SET resulted in the original error.

    Instead I ditched my attempt to set %PATH% entirely and just used the found reference to SqlCmd.exe directly. The final batch file is like so;

    @echo off

REM Ensure we can find the sql interpreter
echo Checking for SqlCmd.exe, please wait...
setlocal EnableExtensions
setlocal EnableDelayedExpansion
for /f "delims=" %%J in ('dir "%programfiles%\sqlcmd.exe" /s /b') do (
    echo SqlCmd found at %%~dpJ
    set sqlcmd="%%J"
    goto exit
)

:exit
%sqlcmd% /?

    For those looking to still set the %PATH% variable, and read up on variable expansion, etc, Mofi's links are listed below for convenience;