Regular expression to validate sql's Post: 302856069

Sponsored Content

Top Forums Shell Programming and Scripting Regular expression to validate sql's Post 302856069 by hitmansilentass on Sunday 22nd of September 2013 07:33:40 PM

09-22-2013

Registered User

Regular expression to validate sql's

Hi,

I have 2 different templates of sql's in a single file. From each sql I am trying to extract different segments of the sql's.
Please note that the number of fields and conditions would vary based on the sql. It might not adhere to a single format as below(but will adhere to allowable sql formats).

Below is the sample sql from which I have to identify the following

Code:

macroname      |REPLACE MACRO macroname
AS
(

/*  Comments  */

UPDATE tgt.table targ
FROM
(
SELECT
 tmp.col1
 ,tmp.col2
 ,tmp.col3

FROM src.table tmp
 INNER JOIN tgt.table tgt
 ON tmp.key1 = tgt.key1    AND
    tmp.key2 = tgt.key2  
	AND tmp.key3 = 
	tgt.key3    
	AND
    tgt.dt = '2899-12-31'
 
 CROSS JOIN ctltable tdc
--some comments

WHERE
 col1 =
 col1
 col2=col2
 col3=
 
 col3

) innertab

SET dt = currentdate

WHERE targ.col1 = innertab.col1 AND
      targ.col2 = innertab.col2 AND
      targ.col3 = innertab.col3 AND
      targ.dt = '2899-12-31'
;
);
macro2             |REPLACE MACRO macro2
AS
(
/*########################################################################*/
/*                                                                        */
/*########################################################################*/

INSERT INTO tgt.table tab
  (
     col1,
    col2
    ,col3
    ,col4
    ,col5
    ,col6
  )
SELECT
   col1
  ,col2
  ,col3
  ,col4
  ,col5
  ,col6
FROM
  stg.table                  stg
  LEFT OUTER JOIN
  tgt.table                      tgt
        ON   ( ( stg.col1 = tgt.col1 ) 
        AND    (stg.col2 = tgt.col2 ) )
        AND tgt.col3 =  '2899-12-31'
  CROSS JOIN
  ctltable              tdc
WHERE
  (    tab.col1 IS NULL
   AND tab.col2 IS NULL
  ); 
);

1. whether the sql is an update or an insert query
2. from update query I have to identify the inner query and validate if the query is not having any derivations or any additional joins from the one's given. Basically the joins should always be between 2 tables stg or temp table with the target table only. the target table is identified from the outermost insert or update statement. And stg or temp is identified from the schema names like stg.
3. I have to extract each of the segments from the sql. like insert, update, sub queries, where, set.

can you please help me finding a regular expression to identify the sql's which does not comply to my above definition.

Last edited by hitmansilentass; 09-22-2013 at 11:38 PM.. Reason: CODE tags not QUOTE tags

hitmansilentass

View Public Profile for hitmansilentass

Find all posts by hitmansilentass

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Regular Expression + Aritmetical Expression

Is it possible to combine a regular expression with a aritmetical expression? For example, taking a 8-numbers caracter sequece and casting each output of a grep, comparing to a constant. THX!

2. Linux

Regular expression to extract "y" from "abc/x.y.z" .... i need regular expression

Regular expression to extract "y" from "abc/x.y.z"

3. Shell Programming and Scripting

validate a string against a regular expression

Hi there i have a script which will create unix user accounts. Id like to validate the entered string so that it is specifically 8 characters or less and consists of only ! not Is there a way to validate a string against a regular expression.. i.e size=`printf "$var | wc -m` ...

4. Shell Programming and Scripting

validate date pattern using Regular Expression

Hi, i am java guy and new to unix. I want to validate date pattern using Regex expression here is the sample program i have written. #!/bin/sh checkDate="2010-04-09" regex="\\d{4}-\\d{2}-\\d{2}\$" echo $regex if ] then echo "OK" else echo "not OK" fi But the ouput is...

5. Shell Programming and Scripting

Validate time pattern using regular expression

Hi, I am new to scripting. please help me in validating the user entered time Pattern Here is the program #!/bin/bash validateTimeFormat() { checkTime=$1 timePattern="::" if ] then echo "Valid time pattern" return 1 else echo "InValid time pattern" return -1 fi } echo "Please...

6. Shell Programming and Scripting

Integer expression expected: with regular expression

CA_RELEASE has a value of 6. I need to check if that this is a numeric value. if not error. source $CA_VERSION_DATA if * ] then echo "CA_RELESE $CA_RELEASE is invalid" exit -1 fi + source /etc/ncgl/ca_version_data ++ CA_PRODUCT_ID=samxts ++ CA_RELEASE=6 ++ CA_WEEK_NO=7 ++...

7. Programming

Perl: How to read from a file, do regular expression and then replace the found regular expression

Hi all, How am I read a file, find the match regular expression and overwrite to the same files. open DESTINATION_FILE, "<tmptravl.dat" or die "tmptravl.dat"; open NEW_DESTINATION_FILE, ">new_tmptravl.dat" or die "new_tmptravl.dat"; while (<DESTINATION_FILE>) { # print...

8. UNIX for Advanced & Expert Users

sed: -e expression #1, char 0: no previous regular expression

Hello All, I'm trying to extract the lines between two consecutive elements of an array from a file. My array looks like: problem_arr=(PRS111 PRS213 PRS234) j=0 while } ] do k=`expr $j + 1` sed -n "/${problem_arr}/,/${problem_arr}/p" problemid.txt ---some operation goes...

9. Shell Programming and Scripting

Need help on regular expression

Hi , I am trying to write a mod_header module rule which will look a specific url (https://partner.testing.com) and rewrite it. The header line is given below. where the url comes in between of the line. i know ^ expression can be used for match the beginning of the line. but not sure how to...

10. Fedora

Use of regular expression

Hi, I need some help. My task is, to write a "one-line" command, which must use ls and awk. Task: Write a command-line, which should rename all files in dir from form "value1.dok" to "value2.doc". And value2=value1+1. For example: ls | awk -F: '{print "mv "$0" "$1+1".doc"}' | sh But...

LEARN ABOUT DEBIAN

mdb-sql

MDBTools(1)															       MDBTools(1)

NAME

       mdb-sql - SQL interface to MDB Tools

SYNOPSIS

       mdb-sql [-HFp] [-d delimiter] [-i file] [-o file] [database]

DESCRIPTION

       mdb-sql is a utility program distributed with MDB Tools.

       mdb-sql allows querying of an MDB database using a limited SQL subset language.

OPTIONS

       -H     Supress header row.

       -F     Supress footer row.

       -p     Turn  off  pretty printing. By default results are printed in an ascii table format which looks nice but is not conducive to manipu-
	      lating the output with unix tools. This option prints output plainly in a tab separated format.

       -d     Specify an alternative column delimiter. If no delimiter is specified, columns will be delimited by a tab character if pretty print-
	      ing (-p) is turned off. If pretty printing is enabled this option is meaningless.

       -i     Specify an input file. This option allows an input file containing the SQL to be passed to mdb-sql.  See Notes.

       -o     Specify an output file. This option allows the name of an output file to be used instead of stdout.

COMMANDS

       mdb-sql in interactive mode takes some special commands.

       connect to <database>
	      If  no database was specified on the command line this command is necessary before any querys are issued. It also allows the switch-
	      ing of databases once in the tool.

       disconnect
	      Will disconnect from the current database.

       go     Each batch is sent to the parser using the 'go' command.

       reset A batch can be cleared using the 'reset' command.

       list tables
	      The list tables command will display a list of available tables in this database, similar to the mdb-tables utility on  the  command
	      line.

       describe table <table>
	      Will display the column information for the specified table.

       quit   Will exit the tool.

SQL LANGUAGE

       The  currently  implemented  SQL  subset is quite small, supporting only single table queries, no aggregates, and limited support for WHERE
       clauses. Here is a brief synopsis of the supported language.

       select:
	      SELECT [* | <column list>] FROM <table> WHERE <where clause>

       column list:
	      <column> [, <column list>]

       where clause:
	      <column> <operator> <literal> [AND <where clause>]

       operator:
	      =, =>, =<, <>, like, <, >

       literal:
	      integers, floating point numbers, or string literal in single quotes

NOTES

       When passing a file (-i) or piping output to mdb-sql the final 'go' is optional. This allow constructs like

       echo "Select * from Table1" | mdb-sql mydb.mdb

       to work correctly.

       The -i command can be passed the string 'stdin' to test entering text as if using a pipe.

ENVIRONMENT

       MDB_JET3_CHARSET
	      Defines the charset of the input JET3 (access 97) file. Default is CP1252. See iconv(1).

       MDBICONV
	      Defines the output charset. Default is UTF-8. mdbtools must have been compiled with iconv.

       MDBOPTS
	      semi-column separated list of options:

	      o  use_index

	      o  no_memo

	      o  debug_like

	      o  debug_write

	      o  debug_usage

	      o  debug_ole

	      o  debug_row

	      o  debug_props

	      o  debug_all is a shortcut for all debug_* options

HISTORY

       mdb-sql first appeared in MDB Tools 0.3.

SEE ALSO

       gmdb2(1) mdb-export(1) mdb-hexdump(1) mdb-prop(1) mdb-ver(1) mdb-array(1) mdb-header(1) mdb-parsecsv(1) mdb-schema(1) mdb-tables(1)

AUTHORS

       The mdb-sql utility was written by Brian Bruns.

BUGS

       The supported SQL syntax is a very limited subset and deficient in several ways.

0.7								   13 July 2013 						       MDBTools(1)