CODEWITHSUNDEEP
sqlwindowsbatch-fileescapingquote
Paul Z
Paul Z

Reputation: 37

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

Upvotes: 1

Views: 2454

Answers (2)

aschipfl
aschipfl

Reputation: 34919

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.

Upvotes: 2

Paul
Paul

Reputation: 2710

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:'=\'!

Upvotes: 0

Related Questions