s/CAST(CAST(hs_date ...) AS CHAR(08)) as .../... whatever .../g

... backquoting all of those from a shell prompt is going to get ugly.

Update: See Quote and Quote-like Operators (buried in the middle of the section) in perlop for a discussion of \l \u \L \U \F \Q \E escapes.

I am trying to update my sql which was written for Teradata DB to be compatible to the DVT. DVT does not accept CAST(CAST(hs_date AS DATE FORMAT 'YYYYMMDD') AS CHAR(08)) as hs_date and only accepts FORMAT_DATE(hs_date, 'YYYYMMDD') as hs_date. I have more than 75 scripts which need to be replaced and compatible to DVT. I have identified these scripts and rather than open each script and replace the string, I thought I could do this from the command line

.....
$sql = "select CAST(CAST(hs_date AS DATE format 'YYYYMMDD') AS CHAR(08
+)) as hs_date, " .
   "device, " .
   "thrshld as CPU_threshold, " .
...
[download]

I have more than 75 scripts which need to be replaced and compatible to DVT. ... I thought I could do this from the command line

This sounds like more than just a quick-and-dirty, one-off command-line task. Write yourself a script. Put all the filenames to be processed into a file and pass that filename to your script. Open each file, make a verified backup first, make all changes (and there will probably be others you need to do; these can easily be added to your script), then save the altered file. Only process files that are copies of files saved elsewhere!

I'd like to provide a pseudo-script example, but I don't have time ATM. Maybe later if no one else steps in first. Just remember that the \Q...\E approach fails if there is any variation in spacing in the substrings to be altered or if they are broken over multiple lines. In that case, a broader approach is needed, but can easily be integrated into a general script.

---

Give a man a fish:  <%-{-{-{-<

This is what I tried but did not work

 perl -p -i.bak -e "s/\QCAST(CAST(hs_date AS DATE FORMAT 'YYYYMMDD') AS CHAR(08)) as hs_date\E/FORMAT_DATE(hs_date, 'YYYYMMDD') as hs_date/g;" HealthList

What is your data?

Win8 Strawberry 5.8.9.5 (32)  Tue 10/13/2020 15:55:21
C:\@Work\Perl\monks\pragovnj
>cat junque.txt
foo bar CAST(CAST(hs_date AS DATE FORMAT 'YYYYMMDD') AS CHAR(08)) as h
+s_date biz boz

C:\@Work\Perl\monks\pragovnj
>perl -p -i.bak -e "s/\QCAST(CAST(hs_date AS DATE FORMAT 'YYYYMMDD') A
+S CHAR(08)) as hs_date\E/FORMAT_DATE(hs_date, 'YYYYMMDD') as hs_date/
+g;" junque.txt

C:\@Work\Perl\monks\pragovnj
>cat junque.txt
foo bar FORMAT_DATE(hs_date, 'YYYYMMDD') as hs_date biz boz

C:\@Work\Perl\monks\pragovnj
>cat junque.txt.bak
foo bar CAST(CAST(hs_date AS DATE FORMAT 'YYYYMMDD') AS CHAR(08)) as h
+s_date biz boz
[download]

(Sorry for any wrap-around. :) In general, "it doesn't work" is a very poor problem description.

Update: Please see Short, Self-Contained, Correct Example for a discussion of good problem presentation practices.

---

Give a man a fish:  <%-{-{-{-<