Check for text variable substring in environment variable

Question

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

Would be useful if you posted the actual name(s) of the Microsoft-containing directorynames from your path so we can apply practice in place of theory. Some problems with your code: filepath will have a terminal backslash. Path elements will usually be of the form d:\dirname; or d:\dirname\; & the last element may not be terminated by ;. Echoing %path%; would seem a good idea, as would /c:"!filepath!;" /c:"!filepath:~0,-1!;" in the 'findstr`. — Magoo
– Magoo, Commented Apr 19, 2023 at 17:10
...and on general principles, use set "var=value" for setting string values - this avoids problems caused by trailing spaces. Don't assign " or a terminal backslash or Space. Build pathnames from the elements - counterintuitively, it is likely to make the process easier. — Magoo
– Magoo, Commented Apr 19, 2023 at 17:10
There should be deleted setlocal EnableDelayedExpansion or modified to setlocal EnableExtensions DisableDelayedExpansion as delayed variable expansion is not really needed here on removing next set filepath=%%~dpF and use everywhere instead of !filepath! %%~dpF. There is a space missing in the dir command line between /s and /b. cmd.exe detects that syntax error after several file system accesses and automatically corrects the error, but better is using the correct syntax with a space between each argument passed to command DIR. — Mofi
– Mofi, Commented Apr 19, 2023 at 17:27
Use echo "%path%" | %SystemRoot%\System32\findstr.exe /I /C:"%%~dpF" 1>nul because of %path% expands to a string containing ) which is interpreted outside of a double quoted argument string as FOR command block closing and not ) at end of the posted batch file which is the cause for the error message. — Mofi
– Mofi, Commented Apr 19, 2023 at 17:30
I recommend to read How does the Windows Command Interpreter (CMD.EXE) parse scripts? and debugging a batch file to better understand how the posted command lines are processed and such syntax issues. The usage of echo "%path%" is in real no solution on PATH contains a folder path enclosed in " because of containing a semicolon. That is fortunately very rare. Better would be path | %SystemRoot%\System32\findstr.exe /I /L /C:"%%~dpF" 1>nul as path is also a command and not only a variable. — Mofi
– Mofi, Commented Apr 19, 2023 at 19:01

DiskJunky · Accepted Answer · 2023-04-20 08:30:25Z

0

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;

How does Windows Commadn Interpreter (CMD.EXE) parse scripts?
Debugging a batch file
Issue 7: Usage of letter ADFNPSTXZadfnpstxz as loop variables
How to set PATH environmetn variable in batch file only once on Windows?
How to check if directory exists in %PATH% (this is the page you're looking for if you want to set %PATH%)

edited Apr 20, 2023 at 8:30

answered Apr 19, 2023 at 19:27

DiskJunky

5,0213 gold badges45 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mofi Over a year ago

I suggest reading this answer for details about the commands SETLOCAL and ENDLOCAL and do not run this command twice first for enabling command extensions and second for enabling delayed variable expansion. Enabled delayed expansion is not necessary for the code in posted answer but it results in folder paths containing by chance one or more ! to be not correct processed by the commands inside the FOR loop. Exclamation marks in the fully qualified file name are interpreted as beginning/end of delayed expanded variable reference.

Collectives™ on Stack Overflow

Check for text variable substring in environment variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related