Escaping ! < and > within a SET statement that is then piped

[colinhay]

Trying to set a variable to an SQL statement with a != in it in a DOS batch file
but am unable to escape the ! so that both the set and the pipe pass the SQL correctly. No number of ^'s prior to the ! make any difference.
Using <> instead of != is just as problematic ....

Running with enabledeleyexpansion on

set SQL=select policy_id from plcy ^
where policy_id != 0 ^
\p\g

echo %SQL% | sql database

[Ed Dyreen]

'
The characters you are refering to are 'special' in DOS, they have to be escaped to use them in a variable,
but you need to escape them twice because of the pipe, to prevent them from being 'interpreted' and 'executed'.

Code: Select all

@echo off &setlocal enableDelayedExpansion

set $lf=^


::
set ^"$sql=^
select policy_id !$lf!^
from   plcy !$lf!^
where  policy_id ^^^^^^^!= 0 \p\g !$lf!^
and    Using ^^^^^<^^^^^> instead of ^^^^^^^!= is just as problematic ...."

  echo.!$sql!
::echo.!$sql! |sql database

pause
exit

Code: Select all

select policy_id
from   plcy
where  policy_id ^!= 0 \p\g
and    Using ^<^> instead of ^!= is just as problematic ....
Druk op een toets om door te gaan. . .

The linefeeds are just for readability, you can remove them though sql shouldn't have any problems when you leave them in.

Regards,

[jeb]

Ed, you are wasting some carets
You take seven carets instead of only six in front of the exclamation marks.

jeb

[Ed Dyreen]

[edit by Ed Dyreen on 27 Jul 2012] corrected an error at line 19; added an extra exclamation mark so all variables retract properly.
It's like a bad habbit innit
I inherit this behavior from our delay algo we develop : delay ! = '^' * 2 + '^!'

Code: Select all

:: --------------------------------------------------------------------------------------------------------------------------
set "$defines=reDelay_" &set "$details="
:: --------------------------------------------------------------------------------------------------------------------------
:: last updated       : 10/03/2012
:: support            : naDelayed, no $lf, $cr
::
2>nul ( %macroStart_% enableDelayedExpansion )
:: (
%=       =%set ^"!$defines!=!forQ_! (1,2) do if %%?==2 (%$n1c%
%=              =%!n2echo_! !$defines!: '^^^!$^^^!'%$n1c%

%=              =%!necho_!  $notDelayedFlag: '^^^!?^^^!'%$n1c%
%=              =%!forQ_! (^^^!$^^^!) do set $=^&set $=^^^!%%~?^^^!^&^&(!necho_!  %%~?: '^^^!$^^^!'%$n1c%
%=                     =%set $=^^^!$:^^^"=""^^^!%=                                            pre-delay         =%%$n1c%
%=                     =%call set "$=%%^^^$:^^^!=#"#"^^^!%%"^^^!%=                            delay 2-times     =%%$n1c%
%=                     =%if defined ? (set "$=^!$:^^^=^^^^^!"^&set "$=^!$:#"#"=^^^!")else set "$=^!$:^^^=^^^^^^^^^!"^&set "$=^!$:#"#"=^^^^^^^!"%$n1c%
%=                     =%set $=^^^!$:""=^^^"^^^!%=                                            post-delay        =%%$n1c%
%=                     =%!necho_!  %%~?: '^^^!$^^^!'%$n1c%
%=                     =%!forLineR_! ("^!$^!") do endlocal^&set "%%~?=%%~r"^^^!^&setlocal enableDelayedExpansion%$n1c%
%=              =%)%$n1c%

%=       =%endlocal^&!rErr!^&!nErr!)else set ?=^^^!^&setlocal enableDelayedExpansion^&set $="
:: )
2>nul %macroEnd%
%EndlocalR_% (
%$%
)

But you are right, as it saves a few bites on disk, but not in memory.
Don't execute this !, I try to post a small include file for those interested...

[colinhay]

Thanks for the reply - I'm sure I had tried that but in the end I rewrote the SQL to

where not (policy_id = 0)