i trying split string clause of sql statement array 5 outputs following data hold under each index:
0 - initial clauses (where/and/or) plus open brackets. e.g "and(((" 1 - either table first clause comes or "value" if value. e.g. "transactions". 2 - field name or value. e.g. "id" 3 - joining value. e.g. > 4 - either table second clause comes or "value" if value. e.g. "transactions". 5 - field name or value. e.g. "id" 6 - closing brackets. e.g. ")))" for example looping through following string output following arrays:
where transactions.status_code= 'afa 2' , (transactions.supp_ref = supplier.supp_ref , supplier.supp_addr_ref = address.addr_ref) or transactions.user_code = user.user_code output[0] = "where" output[1] = "transactions" output[2] = "status_code" output[3] = "=" output[4] = "value' output[5] = "afa 2" output[6] = "" output[0] = "and(" output[1] = "transactions" output[2] = "supp_ref" output[3] = "=" output[4] = "supplier" output[5] = "supp_ref" output[6] = "" output[0] = "and" output[1] = "supplier" output[2] = "supp_addr_ref" output[3] = "=" output[4] = "address" output[5] = "addr_ref" output[6] = ")" output[0] = "or" output[1] = "transactions" output[2] = "user_code" output[3] = "=" output[4] = "user" output[5] = "user_code" output[6] = "" for rest of sql statement have split in similar ways using string.split method, due variances of clause having difficulties doing on part. looking around think better using regex expression, cant work out whats needed. or direction appreciated.
ok, first off think regex might not best fit trying do. being said here regex parse have posted , turn looking for:
(?<group>(?<concat>where|\s*?\)?\s*?and\s*?\(?|\s*?\)?\s*?or\s*?\(?)(?<tablename>[\w\s]+(?=\.))\.?(?<colname>.+?(?=\=|like|between|\<\>|\>\=|\<\=|in|\>|\<))\s*?(?<compare>\=|like|between|\<\>|\>\=|\<\=|in|\>|\<)(?<value>.*?(?=\s*?and\s*?\(*|or\*?\(*)|.*)) i'm sure doesn't cover , depending on regex parser might behave differently. use the regulator regex work.
i suggest writing parser this. take @ have below, might if decide go route. wasn't entirely sure doing "value" string have there if looking identify value , table.colname add this. it's going harder identify things in ('a', 'b') think dooable.
//a list of chars going replace \s"char"\s list may not complete. // . not in here. take care of later. static string[] specchars = new string[] { "<", ">", "<=", ">=", "=", "like", "in", "between", "or", "and", "(", ")", "where" }; static string[] delims = new string[] {"and", "or", "where" }; static string testdata = @"where transactions.status_code= 'afa 2' , (transactions.supp_ref = supplier.supp_ref , supplier.supp_addr_ref = address.addr_ref) or transactions.user_code = user.user_code"; static void main(string[] args) { print(parse(testdata)); console.readkey(); } static list<list<string>> parse(string input) { list<list<string>> ret = new list<list<string>>(); //lets remove spaces first becaue going put them //the way want see them. input = input.replace(" ", "").replace("\r", "").replace("\n", "").tolower(); foreach (string item in specchars) { //this clean string can use input = input.replace(item, string.format(" {0} ", item)); } string[] splits = input.split(' '); list<string> currlist = null; foreach (string item in splits.where(x => x.length > 0)) { if (delims.contains(item)) { if (currlist != null) { ret.add(currlist); currlist = new list<string>(); currlist.add(item); } else { currlist = new list<string>(); currlist.add(item); } } else { if (item.contains(".")) { string[] tmp = item.split('.'); currlist.add(tmp[0]); currlist.add(tmp[1]); } else currlist.add(item); } } if (currlist != null) ret.add(currlist); return ret; } static void print(list<list<string>> input) { stringbuilder sb = new stringbuilder(); foreach (list<string> item in input) { sb.append("new chunk:\n"); foreach (string str in item) { sb.append(string.format("\t{0}\n", str)); } sb.append("\n"); } console.writeline(sb.tostring()); } } 
Comments
Post a Comment