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

Question

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"?

Provide more code, please; where do all the `%%` variables come from?? — aschipfl, Oct 06 '15 at 21:47
I used: following string replacement and it does not work: set %%b=%%%b:'=''% set %%a=%%%a:'=''% set %%f=%%%f:'=''% set %%c=%%%c:'=''% set %%d=%%%d:'=''% — Paul Z, Oct 07 '15 at 01:09
You cannot apply the string replacement syntax `%VAR:str=rpl%` to `for` variables, you'll need an interim "normal" variable, but with delayed expansion due to `for`... and insert a space in between `do` and `(`... — aschipfl, Oct 07 '15 at 01:20
This is the first time I write window batch. Could you show me a couple of lines of sample codes? — Paul Z, Oct 07 '15 at 01:38
I just summarised all that in an [answer](http://stackoverflow.com/a/32982411/5047996)... — aschipfl, Oct 07 '15 at 01:39
Are you sure that you actually want a QUOTE character in the last name string rather than an APOSTROPHE? I do not know anyone named `O"Brien`. — lit, Oct 07 '15 at 02:58

aschipfl · Accepted Answer · 2019-11-18T23:22:41.120

2

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.

edited Nov 18 '19 at 23:22

answered Oct 07 '15 at 01:38

aschipfl

33,626
12
54
99

I replaced with 2 single quotes and it works: set "c_tmp=%%c" & set "c_tmp=!c_tmp:'=''!" – Paul Z Oct 07 '15 at 03:00
Ah, apparently I misread `''` as `"`... anyway, you got the idea how it works and fidured it out yourself... so I adapted my answer to comply with that... – aschipfl Oct 07 '15 at 08:28
I would like to do the same escaping, but my input is a environment variable, not a file. I have ```set "Name=O'Brien"``` so if i do ```echo %Name%``` the output is ```O'Brien```. How do I define a SafeName variable so that if i do ```echo %SafeName%``` the output is ```O''Brien``` (that is the content of Name but with each apostrophe replaced by two apostrophes)? --- EDIT: Apparently it's as simple as ```set "Safename=%Name:'=''%"``` – Fry Simpson Mar 02 '20 at 11:56

score 0 · Answer 2 · answered Oct 07 '15 at 03:02

Perhaps you could escape single quote with \ like O\'brian if it's sqlcmd failing to insert.

If the file containing single quotes that does not use for delimitation of values, then you could replace all occurrences in an intermediate file.

(
echo O'Brian1a, O'Brian1b, O'Brian1c, O'Brian1d, O'Brian1e, O'Brian1f
echo O'Brian2a, O'Brian2b, O'Brian2c, O'Brian2d, O'Brian2e, O'Brian2f
echo O'Brian3a, O'Brian3b, O'Brian3c, O'Brian3d, O'Brian3e, O'Brian3f
)>%tmp%\myfile.tmp

( 
  for /f "delims=" %%i in (%tmp%\myfile.tmp) do (
    set "i=%%i"
    setlocal enabledelayedexpansion
    echo !i:'=\'!
  )
)>%tmp%\int-myfile.tmp

for /f "tokens=1-6 delims=, " %%a in (%tmp%\int-myfile.tmp) do ( 
  echo '%%a','%%b','%%c','%%d','%%e','%%f'
)

output:

'O\'Brian1a','O\'Brian1b','O\'Brian1c','O\'Brian1d','O\'Brian1e','O\'Brian1f'
'O\'Brian2a','O\'Brian2b','O\'Brian2c','O\'Brian2d','O\'Brian2e','O\'Brian2f'
'O\'Brian3a','O\'Brian3b','O\'Brian3c','O\'Brian3d','O\'Brian3e','O\'Brian3f'

As a last resort you can always choose with which character you replace in the line: echo !i:'=\'!

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

2 Answers2