query to find the non alphabets characters in mysql using regular expression

mysq> create table house ( eno int not null auto_increment primary key, name varchar(100));

mysql > insert into house(name) values ('kiran1$');
mysql > insert into house(name) values ('kiran$$$');
mysql > insert into house(name) values ('ki###ran');
mysql > insert into house(name) values ('kiranan');
mysql > insert into house(name) values ('###kran');


mysql> select * from house where name NOT REGEXP '^[A-Za-z0-9]+$';
+-----+----------+
| eno | name     |
+-----+----------+
|   1 | kiran1$  |
|   2 | kiran$$$ |
|   3 | ki###ran |
|   5 | ###kran  |
+-----+----------+
4 rows in set (0.00 sec)

Example 1 is as follows:

SELECT col1, col2 FROM table1
WHERE col0 REGEXP '^[0-9]+IFJ[0-9]*';
In the regular expression in this example, the ^ indicates that the pattern must start at the beginning of this string.
Next, [0-9] indicates that a number from 0 to 9 is required next.
The plus sign following it means that one or more numbers are expected.
After this, the actual letters IFJ must be matched.
Finally, because of the asterisk after the [0-9],
zero or more numbers may follow.
This SQL statement will match an identifier like 123IFJ and 123IFJ456, but not IFJ123.



Below is a table listing the regular expression operators available:
* zero or more of preceding character
+ one or more of preceding character
{n} number of occurences of preceding character
{n,n} from first to second number of occurences of preceding character
^ beginning of string
$ end of string
. any one character
[...] characters within brackets
[^...] not the characters within brackets
...|... alternative patterns
For more details the url is as follows:

http://dev.mysql.com/doc/refman/5.5/en/regexp.html
http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

query to find the non alphabets characters in mysql using regular expression

Executing multiple sql files and know the errors and warning messages after executing commands mysql

First all the script files and path should store in one file as follows

The script_list.txt contains the following files

cat /home/kiran.chinta/prodscripts/script_list.txt

testdb1 /home/scripts/insert1.sql
testdb2 /home/scripts/scripts2.sql
testdb3 /home/scripts/testsql.sql

vi script.sh


#!/bin/sh
# script to generate the correct commands to execute the sql statements from a text file
# the text file script_list.txt should start with schema_name followed by a space and then script_file_path
# disable echo command and the double quotes to execute the statements
myhost='127.0.0.1'
myuser='kiran'
mypass='kiran@123'
myport='3306'
mylog='/home/kiran.chinta/prodscripts/logs'
mkdir -p $mylog
cat /home/kiran.chinta/prodscripts/script_list.txt | while read -r myschema filename
do
if [ -s $filename ];then
mybase=`basename $filename`
time mysql -h$myhost -u$myuser -p$mypass $myschema -vvv -f --show-warnings  < $filename > "$mylog"/"$myschema"_"$mybase"_success.txt 2> "$mylog"/"$myschema"_"$mybase"_err.txt
#echo "$myschema $filename  "$mylog"/"$myschema"_"$mybase"_success.txt "$mylog"/"$myschema"_"$mybase"_err.txt  "
else
echo " FILE $filename DOES NOT EXIST"
fi


### for executing the above scripts the command is as follows
sh script.sh > success.txt 2> failure.txt