Using a shell script variable in a mysql query using 'LIKE'

Shell Programming and Scripting
1

Hello. I am writing a simple script that reads a text file and removes records from a mysql database. The items in the text file are of the format:

firstname.middle.lastXXX, where XXX is a 3 digit number. The table has an email field that will match the firstname.middle.last. So, I thought I would use a LIKE statement and the corresponding wildcard (%) at the end. Here is what I have tried:

for x in `cat /etc/names.txt`
do
 
   echo "delete from main where email LIKE '$x . "%"'" | mysql -h my.server.name --user=myuser --password='mypass'
done

The problem is with the wildcard % and using it with the $x variable. I have tried different formats of this and can't make it work. I have searched around and have seen different solutions, but none have worked for me. I welcome ideas that will get me beyond this...thanks.

2

Your query will be formed like this I guess which is not what you want

delete from main where email LIKE firstname.middle.lastXXX%

You may want to strip the 3 digit from $x.

for x in `cat /etc/names.txt`
do
name=`echo $x | tr -d [:digit:]`
echo "delete from main where email LIKE $name" | mysql -h my.server.name --user=myuser --password=mypass
done

regards,
Ahamed