In Windows batch – how do I replace single quote with escape single quote to feed SQL

With my Windows batch file, I have following SQL statement:

FOR /F "eol=; tokens=1,2,3,4,5,6* delims=, " %a in (myfile.txt) do(
    sqlcmd -SmYSerbver  -Uhhh -P12345 -dmyDB_Admin -Q"insert into tableA (UserID,FirstName,LastName,Email,DisplayName,OrgUnit,LoadDate) values('%%a','%%b','%%c','%%d','%%e','%%f',getdate())"
)

One user’s last name is “O’Brien” – my variable %%c is evaluated as O’Brien.

How do I operate to make %%c evaluated as “O”Brien”?

Answer

You need an interim variable to do string replacements as the related syntax cannot be applied to for variables like %%c directly. For this to work, delayed variable expansion needs to be enabled, and the interim variable needs to be expanded using !! rather than %%.

The following illustrates how to accomplish that for the value in %%c, using the interim variable c_tmp:

setlocal EnableDelayedExpansion
for /F "eol=; tokens=1,2,3,4,5,6* delims=, " %%a in (myfile.txt) do (
    set "c_tmp=%%c" & set "c_tmp=!c_tmp:'=''!"
    sqlcmd -SmYSerbver  -Uhhh -P12345 -dmyDB_Admin -Q"insert into tableA (UserID,FirstName,LastName,Email,DisplayName,OrgUnit,LoadDate) values('%%a','%%b','!c_tmp!','%%d','%%e','%%f',getdate())"
)
endlocal

When %%c contains a string O'Brien, c_tmp will finally contain O''Brien.

Of course you can do also other replacements by modifying the command set "c_tmp=!c_tmp:'=''!" accordingly.