windows .bat script to replace a string with a dynamic integer in it

Question

I have an .sql file and want to replace all lines like this:

VALUES (12,
VALUES (24,
VALUES (9999,

with

VALUES (NULL,

I have tried like this, but the regex does not seem to work and the output does not change

@echo off
setlocal enabledelayedexpansion

set "replacement=VALUES (NULL,"
set "regex=VALUES \(\d+,"
set "output_file=updated_sql_dump.sql"

(for /f "delims=" %%i in ('type "MONITORING-schema.sql" ^| findstr /n "^"') do (
    set "line=%%i"
    echo !line! | findstr /r /c:"%regex%" >nul && (
        echo !line:%search%=%replacement%!
    ) || (
        echo !line:*:=!
    )
))

Answer 1

I propose the following:

@ECHO OFF
SETLOCAL enabledelayedexpansion
:rerun
REM Remove the result file from a previous run
SET inputFile=inputFile.txt
SET outputFile=outputFile.txt
DEL %outputFile%

REM create a test file and fill with some data
ECHO VALUES (12,>%inputFile%
ECHO VALUES (24,>>%inputFile%
ECHO Some other line>>%inputFile%
ECHO VALUES (9999,>>%inputFile%
ECHO Input file:
ECHO.
TYPE %inputFile%
ECHO.

REM Define a constant string
SET constString=VALUES (
ECHO Constant string: "%constString%"

REM Define a replacement string
SET replaceString=NULL,
ECHO Replacement string: "%replaceString%"
ECHO.

REM Run the find and replace loop on the input file
FOR /f "delims=" %%a IN (%inputFile%) DO ( 
    SET lineInFile=%%a
    ECHO Given line:     "!lineInFile!"
    SET dynamicStrig=!lineInFile:%constString%=!
    ECHO Dynamic string: "!dynamicStrig!"
    IF "!lineInFile!" == "!dynamicStrig!" (
        REM No constant string was found. Input line will be output line
        SET resultString=!lineInFile!
    ) ELSE (
        REM This is the tricky part. Replace the dynamic part of the line with the replacement string
        FOR %%b IN ("!dynamicStrig!") DO SET "resultString=!lineInFile:%%~b=%replaceString%!"
    )
    ECHO New line:       "!resultString!"
    REM Append the line to the output file
    ECHO !resultString!>>%outputFile%
)

ECHO.
ECHO Output file:
ECHO.
TYPE %outputFile%
ECHO.

REM for test purposes rerun the script
PAUSE
GOTO rerun

What it does is generating a dynamic string by replacing the given string with a defined constand string. This dynamic string is now replaced from the given string with the defined replacement string.

The important line is

FOR %%b IN ("!dynamicStrig!") DO SET "resultString=!lineInFile:%%~b=%replaceString%!"

Output:

this is based on this answer: https://superuser.com/a/807645/1850668