0

I've been trying to do a string replacement type of operation in bash. It's not exactly as easy as it seems though, so here's the explanation:

Say you have an SQL file with multiple SELECT statements in it. Each SELECT block ends with a semicolon (;).

My goal is to grab each block separately and perform some operation with it. However, the script should be able to recognize these blocks dynamically because it will be working with different sql files and store each block into a variable (for example $1), so that the block can be easily used later on.

PS I've been battling this issue over the past week and any advice would be much appreciated. Furthermore bellow you can see some of the regex-type-of-thingies I've used so far:

egrep -e "^(\s|SELECT).*.[^;]\s*" test.sql

and this

cat test.sql | awk '" "{found=0} /SELECT/{found=1} {if (found) print }'

The first one works the best and is closest to the goal but I cannot find a way to separate and store the blocks of the SELECT statements. The second one uses a similar expression to find the semicolon. In addition to that i tried to tell the script to use anything between them as a third item and then to try and store the 3 items together as a whole. Couldn't make it work though. SELECT statements are multiline.

  • 1
    Please edit your question and give us an example of your input file. Also tell us if the SELECT statements will always be on a single line or if they can span multiple lines. – terdon Mar 7 '16 at 15:15
  • SELECT statements are on multiple lines and there is no remotely working version of the script i can post on here, but i can post just some random SQL queries if you wish. – Nikolai Uzunov Mar 7 '16 at 15:27
  • Suppose you read the data in a loop, one block at a time. Is it important to you to save any of the information so it can be used outside the loop? Or are all the operations you are going to do on that block going to be inside the loop? – RealSkeptic Mar 7 '16 at 16:26
  • all the operations will be inside that loop. To be more specific: after a block is selected i have to make it humanly readable(ignoring the fact we are talking about sql of all things) and write the translation to a new file. That last part I can do but its the splitting and usage of individual blocks thats hard for me. – Nikolai Uzunov Mar 7 '16 at 16:45
0

You can use a read loop with a delimiter other than "newline". This would cause the read command in bash to consider multiple lines as one, provided that they end in your delimiter.

Note: this method is not suitable if the semicolon might appear in the file inside comments or strings. This is not a parser - just a simple read loop.

while read -rd ';' sql
do
    if [ "${sql#SELECT}" = "$sql" ]
    then
        echo "Not a SELECT!"
    else
        echo "This is the command I got:"
        echo ">>$sql<<"
    fi
done < test.txt 

The read -rd ';' sql is going to read the input until it sees a semicolon. The block is going to be in the variable $sql.

The if inside the loop checks if removing the word SELECT from the beginning of the text in the variable changes it - it's basically asking whether the text does not begin with the word SELECT.

Note that read will remove initial and trailing blanks in each block as it is looking for "words". Empty lines before and after the semicolon are therefore removed.

Example text file:

   SELECT *
FROM PLANTS

WHERE TYPE='BUSH'

;

INSERT INTO PLANTS (TYPE,NAME,ID)
VALUES ('TREE', 'Oak', 15)    ;   

SELECT NAME
FROM PLANTS
WHERE NAME LIKE 'O%';

Will result in the following output:

This is the command I got:
>>SELECT *
FROM PLANTS

WHERE TYPE='BUSH'<<
Not a SELECT!
This is the command I got:
>>SELECT NAME
FROM PLANTS
WHERE NAME LIKE 'O%'<<
  • you sir are awesome. exactly what i was trying to do. – Nikolai Uzunov Mar 8 '16 at 9:22
0
awk -v RS=";" '/SELECT/{print $0";"}'

If a record (where RS is ;) has string SELECT it will be printed along with ; at the end.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.