引入pom
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>3.2</version>
</dependency>上代码 解析sql里面select的字段where里面使用的字段，替换字段
package com.liuhm.demo.utils;
import com.google.common.collect.Lists;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import org.junit.jupiter.api.Test;
import java.util.*;
/**
* 解析sql字符串字段的方法 获取selec的字段 获取where里面使用的字段 替换字段
* @author liuhaomin
* @date 2020/11/25
*/
public class SqlParserUtils {
@Test
public void test22() throws JSQLParserException {
Map<String, String> map=new HashMap<>();
map.put("a1","c1");
map.put("a2","c2");
map.put("a3","c3");
map.put("a4","c4");
map.put("a5","c5");
map.put("srt","c5");
map.put("sfzh","qqqqqqqqqq");
String sql = "select asd(a213),COUNT(COUNT(ss)),REGEXP_MATCH(str, '[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}|[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)'),HOUR(starttime),HOUR(endtime),ADDDATE(endtime,4),a1,TIMESTAMPDIFF(starttime,starttimse,starsdttime),REGEXP_MATCH(username, '(京[A-HJ-NPQY]|沪[A-HJ-N]|津[A-HJ-NPQR]|渝[A-DFGHN]|冀[A-HJRST]|晋[A-FHJ-M]|蒙[A-HJKLM]|辽[A-HJ-NP]|吉[A-HJK]|黑[A-HJ-NPR]|苏[A-HJ-N]|浙[A-HJKL]|皖[A-HJ-NP-S]|闽[A-HJK]|赣[A-HJKLMS]|鲁[A-HJ-NP-SUVWY]|豫[A-HJ-NP-SU]|鄂[A-HJ-NP-S]|湘[A-HJ-NSU]|粤[A-HJ-NP-Y]|桂[A-HJ-NPR]|琼[A-F]|川[A-HJ-MQ-Z]|贵[A-HJ]|云[AC-HJ-NP-SV]|藏[A-HJ]|陕[A-HJKV]|甘[A-HJ-NP]|青[A-H]|宁[A-E]|新[A-HJ-NP-S])([0-9A-HJ-NP-Z]{4}[0-9A-HJ-NP-Z挂试]|[0-9]{4}学|[A-D0-9][0-9]{3}警|[DF][0-9A-HJ-NP-Z][0-9]{4}|[0-9]{5}[DF])|WJ[京沪津渝冀晋蒙辽吉黑苏浙皖闽赣鲁豫鄂湘粤桂琼川贵云藏陕甘青宁新]?[0-9]{4}[0-9JBXTHSD]|(V[A-GKMORTV]|K[A-HJ-NORUZ]|H[A-GLOR]|[BCGJLNS][A-DKMNORVY]|G[JS])[0-9]{5}|[0-9]{6}使|([沪粤川渝辽云桂鄂湘陕藏黑]A|闽D|鲁B|蒙[AEH])[0-9]{4}领|粤Z[0-9A-HJ-NP-Z][0-9]{3}[港澳]') from t where a1 = 't3.g' and (a2=a3 or (a4=ef or a2=2)) and (a2=a3 or (a4=ef or a2=2))";
sql = "select aa as b2,aa b2,COUNT(test),a1,TIMESTAMPDIFF(starttime,starttimse,starsdttime),REGEXP_MATCH(a4, '(京[A-HJ-NPQY]|沪[A-HJ-N]|津[A-HJ-NPQR]|渝[A-DFGHN]|冀[A-HJRST]|晋[A-FHJ-M]|蒙[A-HJKLM]|辽[A-HJ-NP]|吉[A-HJK]|黑[A-HJ-NPR]|苏[A-HJ-N]|浙[A-HJKL]|皖[A-HJ-NP-S]|闽[A-HJK]|赣[A-HJKLMS]|鲁[A-HJ-NP-SUVWY]|豫[A-HJ-NP-SU]|鄂[A-HJ-NP-S]|湘[A-HJ-NSU]|粤[A-HJ-NP-Y]|桂[A-HJ-NPR]|琼[A-F]|川[A-HJ-MQ-Z]|贵[A-HJ]|云[AC-HJ-NP-SV]|藏[A-HJ]|陕[A-HJKV]|甘[A-HJ-NP]|青[A-H]|宁[A-E]|新[A-HJ-NP-S])([0-9A-HJ-NP-Z]{4}[0-9A-HJ-NP-Z挂试]|[0-9]{4}学|[A-D0-9][0-9]{3}警|[DF][0-9A-HJ-NP-Z][0-9]{4}|[0-9]{5}[DF])|WJ[京沪津渝冀晋蒙辽吉黑苏浙皖闽赣鲁豫鄂湘粤桂琼川贵云藏陕甘青宁新]?[0-9]{4}[0-9JBXTHSD]|(V[A-GKMORTV]|K[A-HJ-NORUZ]|H[A-GLOR]|[BCGJLNS][A-DKMNORVY]|G[JS])[0-9]{5}|[0-9]{6}使|([沪粤川渝辽云桂鄂湘陕藏黑]A|闽D|鲁B|蒙[AEH])[0-9]{4}领|粤Z[0-9A-HJ-NP-Z][0-9]{3}[港澳]') from t where a1 = 't3.g' and (a2=a3 or (a4=ef or a2=2)) and (a2=a3 or (a4=ef or a2=2))";
Set<String> stringSet = getSelectItem(sql,true);
System.out.println("getSelectItem "+stringSet);
stringSet = getWhere(sql,true);
System.out.println("getWhere "+stringSet);
String strings = setSelectItem(sql,map,true);
System.out.println("setSelectItem "+strings);
strings = setWhere(sql,map,true);
System.out.println("setWhere "+strings);
sql="FIELD(str,str1,str2,str3)";
stringSet = getSelectItem(sql,false);
System.out.println("getSelectItem "+stringSet);
strings = setSelectItem(sql,map,false);
System.out.println("setSelectItem "+strings);
sql="a1 = 't3.g' and (a2=a3 or (a4=ef or a2=2)) and (a2=a3 or (a4=ef or a2=2))" ;
stringSet = getWhere(sql,false);
sql="((a1 = 't3.g' and (a2=a3 or (a4=ef or a2=2)) and (a2=a3 or (a4=ef or a2=2))))" ;
stringSet = getWhere(sql,false);
System.out.println("getWhere "+stringSet);
strings = setWhere(sql,map,false);
System.out.println("setWhere "+strings);
}
@Test
public void test() throws JSQLParserException {
Map<String, String> map=new HashMap<>();
map.put("a1","c1");
map.put("a2","c2");
map.put("a3","c3");
map.put("a4","c4");
map.put("a5","c5");
selectList.forEach(data->{
System.out.println("---getSelectItem----"+getSelectItem(data,false));
System.out.println("---setSelectItem----"+setSelectItem(data,map,false));
});
whereList.forEach(data->{
System.out.println("---getWhere----"+getWhere(data,false));
System.out.println("---setWhere----"+setWhere(data,map,false));
});
}
/**
* 获取select里面的参数
* @param
* @return
*/
public Set<String> getSelectItem(String sql,boolean isAll){
Set<String> selectItemSet =new HashSet<>();
Statement stmt = null;
if(!isAll){
sql="SELECT "+sql+" FROM t";
}
try {
System.out.println(sql);
stmt = CCJSqlParserUtil.parse(sql);
} catch (JSQLParserException e) {
e.printStackTrace();
}
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
getSelectItem(plainSelect,selectItemSet);
return selectItemSet;
}
/**
* 获取select里面的参数
* @param
* @return
*/
public String setSelectItem(String sql,Map<String, String> map,boolean isAll){
Statement stmt = null;
if(!isAll){
sql="SELECT "+sql+" FROM t";
}
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (JSQLParserException e) {
e.printStackTrace();
}
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
setSelectItem(plainSelect,map);
if(!isAll){
return plainSelect.toString().replace("SELECT ","").replace(" FROM t","");
}
return plainSelect.toString();
}
/**
* 获取select里面的参数
* @param
* @return
*/
public Set<String> getWhere(String sql,boolean isAll){
Set<String> whereItemSet =new HashSet<>();
Statement stmt = null;
if(!isAll){
sql="SELECT * FROM t WHERE "+sql;
}
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (JSQLParserException e) {
e.printStackTrace();
}
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
getWhereItem(plainSelect.getWhere(),whereItemSet);
return whereItemSet;
}
/**
* 获取select里面的参数
* @param
* @return
*/
public String setWhere(String sql,Map<String, String> map,boolean isAll){
Statement stmt = null;
if(!isAll){
sql="SELECT * FROM t WHERE "+sql;
}
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (JSQLParserException e) {
e.printStackTrace();
}
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
setWhereItem(plainSelect.getWhere(),map);
if(!isAll){
return plainSelect.toString().replace("SELECT * FROM t WHERE ","");
}
return plainSelect.toString();
}
/**
* 设置替换select 里面的字段
* @param plainSelect
* @param map
* @return
*/
public void setSelectItem(PlainSelect plainSelect, Map<String, String> map){
for (SelectItem selectItem : plainSelect.getSelectItems()) {
selectItem.accept(new SelectItemVisitorAdapter() {
@Override
public void visit(SelectExpressionItem item) {
if (item.getExpression() instanceof Function) {
setFunction((Function)item.getExpression(),map);
}else if (item.getExpression() instanceof CaseExpression) {
setCaseExpression((CaseExpression)item.getExpression(),map);
}else if (item.getExpression() instanceof Column) {
Column column = (Column) item.getExpression();
column.setColumnName(Optional.ofNullable(map.get(column.getColumnName())).orElse(column.getColumnName()));
}
}
});
}
}
/**
* 获取select里面的字段
* @param plainSelect
* @param selectItemSet
* @return
*/
public void getSelectItem(PlainSelect plainSelect, Set<String> selectItemSet){
for (SelectItem selectItem : plainSelect.getSelectItems()) {
selectItem.accept(new SelectItemVisitorAdapter() {
@Override
public void visit(SelectExpressionItem item) {
if (item.getExpression() instanceof Function) {
getFunction((Function)item.getExpression(),selectItemSet);
}else if (item.getExpression() instanceof CaseExpression) {
getCaseExpression((CaseExpression)item.getExpression(),selectItemSet);
}else if (item.getExpression() instanceof Column) {
Column column = (Column) item.getExpression();
selectItemSet.add(column.getColumnName());
}
}
});
}
}
/**
* 获取select里面function里面的字段
* @param function
* @param selectItemSet
* @return
*/
public void getFunction(Function function, Set<String> selectItemSet){
if(function.getParameters()==null || function.getParameters().getExpressions()==null){
return;
}
List<Expression> list=function.getParameters().getExpressions();
list.forEach(data->{
if (data instanceof Function) {
getFunction((Function)data,selectItemSet);
}else if (data instanceof Column) {
Column column = (Column) data;
selectItemSet.add(column.getColumnName());
}else{
getWhereItem(data,selectItemSet);
}
});
}
/**
* 获取select里面CaseExpression里面的字段
* @param caseExpression
* @param selectItemSet
* @return
*/
public void getCaseExpression(CaseExpression caseExpression, Set<String> selectItemSet){
if(caseExpression.getWhenClauses()==null){
return;
}
List<WhenClause> list=caseExpression.getWhenClauses();
list.forEach(data->{
if (data instanceof WhenClause) {
getWhereItem(data.getWhenExpression(),selectItemSet);
}
});
}
/**
* 设置替换select 中 function里面的字段
* @param function
* @param map
* @return
*/
public void setFunction(Function function, Map<String, String> map){
if(function.getParameters()==null || function.getParameters().getExpressions()==null){
return;
}
List<Expression> list=function.getParameters().getExpressions();
list.forEach(data->{
if (data instanceof Function) {
setFunction((Function)data,map);
}else if (data instanceof Column) {
Column column = (Column) data;
column.setColumnName(Optional.ofNullable(map.get(column.getColumnName())).orElse(column.getColumnName()));
}else{
setWhereItem(data,map);
}
});
}
/**
* 设置替换 select里面CaseExpression里面的字段
* @param caseExpression
* @param map
* @return
*/
public void setCaseExpression(CaseExpression caseExpression, Map<String, String> map){
if(caseExpression.getWhenClauses()==null){
return;
}
List<WhenClause> list=caseExpression.getWhenClauses();
list.forEach(data->{
if (data instanceof WhenClause) {
setWhereItem(((WhenClause) data).getWhenExpression(),map);
}
});
}
/**
* 获取where里面的字段
* @param
* @return
*/
public void getWhereItem(Expression where, Set<String> tblNameSet){
if(where instanceof BinaryExpression) {
BinaryExpression binaryExpression = (BinaryExpression) where;
Expression rightExpression = binaryExpression.getRightExpression() instanceof Parenthesis?((Parenthesis) binaryExpression.getRightExpression()).getExpression(): binaryExpression.getRightExpression();
Expression leftExpression = binaryExpression.getLeftExpression() instanceof Parenthesis?((Parenthesis) binaryExpression.getLeftExpression()).getExpression(): binaryExpression.getLeftExpression();
if (rightExpression instanceof Column) {
Column rightColumn = (Column) rightExpression;
tblNameSet.add(rightColumn.getColumnName());
}if (rightExpression instanceof Function) {
getFunction((Function) rightExpression,tblNameSet);
}else {
getWhereItem(rightExpression,tblNameSet);
}
if (leftExpression instanceof Column) {
Column leftColumn = (Column) leftExpression;
tblNameSet.add(leftColumn.getColumnName());
} if (leftExpression instanceof Function) {
getFunction((Function) leftExpression,tblNameSet);
}else {
getWhereItem(leftExpression,tblNameSet);
}
}else if(where instanceof Parenthesis){
getWhereItem(((Parenthesis) where).getExpression(),tblNameSet);
}
}
/**
* 设置替换where里面的字段
* @param
* @return
*/
public void setWhereItem(Expression where, Map<String, String> map){
if(where instanceof BinaryExpression) {
BinaryExpression binaryExpression = (BinaryExpression) where;
Expression rightExpression = binaryExpression.getRightExpression() instanceof Parenthesis?((Parenthesis) binaryExpression.getRightExpression()).getExpression(): binaryExpression.getRightExpression();
Expression leftExpression = binaryExpression.getLeftExpression() instanceof Parenthesis?((Parenthesis) binaryExpression.getLeftExpression()).getExpression(): binaryExpression.getLeftExpression();
if (rightExpression instanceof Column) {
Column rightColumn = (Column) rightExpression;
rightColumn.setColumnName(Optional.ofNullable(map.get(rightColumn.getColumnName())).orElse(rightColumn.getColumnName()));
}else if(rightExpression instanceof Function) {
setFunction((Function) rightExpression,map);
}else {
setWhereItem(rightExpression,map);
}
if (leftExpression instanceof Column) {
Column leftColumn = (Column) leftExpression;
leftColumn.setColumnName(Optional.ofNullable(map.get(leftColumn.getColumnName())).orElse(leftColumn.getColumnName()));
}else if(leftExpression instanceof Function) {
setFunction((Function) leftExpression,map);
}else {
setWhereItem(leftExpression,map);
}
}
}
List<String> selectList= Lists.newArrayList(
"CHAR_LENGTH(a1)",
"CONCAT(a1,a2)",
"CONCAT(a1,a2)",
"SUBSTRING(a1,1,10)",
"SUBSTRING(a1,1,10)",
"TIMESTAMPDIFF(MONTH,a1,a2)",
"TIMESTAMPDIFF(MONTH,a1,a2)",
"ADDDATE(a1,12)",
"ADDDATE(a1,12)",
"ADDTIME(a1,a2)",
"ADDTIME(a1,a2)",
"HOUR(a1)",
"HOUR(a1)",
"DAY(a1)",
"DAY(a1)",
"YEAR(a1)",
"YEAR(a1)",
"MONTH(a1)",
"MONTH(a1)",
"DAYNAME(a1)",
"DAYNAME(a1)",
"TRIM(a1)",
"TRIM(a1)",
"ELT(a1,a2,a3)",
"FIELD(a1,a2,a3,a4)",
"INSERT(a1,2,4,a2)",
"LENGTH(a1)",
"LOCATE(a1,a2)",
"REPEAT(a1,4)",
"REPLACE(a1,'ww','WW')",
"REVERSE(a1)",
"RIGHT(a1,4)",
"STRCMP(a1,a2)",
"CURDATE()",
"CURTIME()",
"DATEDIFF(a1,a2)",
"DATE_FORMAT(a1, '%H:%i:%s')",
"DAYOFYEAR(a1)",
"LAST_DAY(a1)",
"MINUTE(a1)",
"MONTHNAME(a1)",
"QUARTER(a1)",
"SECOND(a1)",
"STR_TO_DATE(a1,'%m/%d/%Y')",
"SUBDATE(a1,22)",
"SUBTIME(a1,a2)",
"SYSDATE()",
"TIME(a1)",
"TIMEDIFF(a1,a2)",
"TIMESTAMP(a1)",
"TIMESTAMPADD(MINUTE,2,a1)",
"TIME_FORMAT(a1,'%m/%d/%Y')",
"WEEK(a1)",
"DAYNAME(a1)",
"DAYOFMONTH(a1)",
"DAYOFWEEK(a1)",
"WEEKOFYEAR(a1)",
"COALESCE(a1)",
"GREATEST(a1,a2)",
"LEAST(a1,a2)",
"IFNULL(a1,a2)",
"NULLIF(a1,a2)",
"IF(a1,a2,a3)",
"CASE value WHEN a1 THEN 1 ELSE 2 END",
"ABS(a1)",
"CEIL(a1)",
"MOD(a1,a2)",
"POW(a1,a2)",
"RAND(a1)",
"ROUND(a1,a2)",
"SIGN(a1)",
"SQRT(a2)",
"IDMATCH(a1)",
"PLATENUMMATCH(a1)",
"IFPHONE(a1)",
"SUBSTR_IN_SET(a1, a2)",
"REGEXP_MATCH(a1, 'pattern')",
"EXTRACT_IDCARD(a1)",
"EXTRACT_HPHM(a1)",
"EXTRACT_PHONE(a1)",
"EXTRACT_BANK_CARD(a1)",
"SUBSTR_IN_SET(a1, a2)",
"REGEXP_MATCH(a1, 'pattern')",
"REGEXP_MATCH(a1, '[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}|[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)')",
"REGEXP_MATCH(a1, '(京[A-HJ-NPQY]|沪[A-HJ-N]|津[A-HJ-NPQR]|渝[A-DFGHN]|冀[A-HJRST]|晋[A-FHJ-M]|蒙[A-HJKLM]|辽[A-HJ-NP]|吉[A-HJK]|黑[A-HJ-NPR]|苏[A-HJ-N]|浙[A-HJKL]|皖[A-HJ-NP-S]|闽[A-HJK]|赣[A-HJKLMS]|鲁[A-HJ-NP-SUVWY]|豫[A-HJ-NP-SU]|鄂[A-HJ-NP-S]|湘[A-HJ-NSU]|粤[A-HJ-NP-Y]|桂[A-HJ-NPR]|琼[A-F]|川[A-HJ-MQ-Z]|贵[A-HJ]|云[AC-HJ-NP-SV]|藏[A-HJ]|陕[A-HJKV]|甘[A-HJ-NP]|青[A-H]|宁[A-E]|新[A-HJ-NP-S])([0-9A-HJ-NP-Z]{4}[0-9A-HJ-NP-Z挂试]|[0-9]{4}学|[A-D0-9][0-9]{3}警|[DF][0-9A-HJ-NP-Z][0-9]{4}|[0-9]{5}[DF])|WJ[京沪津渝冀晋蒙辽吉黑苏浙皖闽赣鲁豫鄂湘粤桂琼川贵云藏陕甘青宁新]?[0-9]{4}[0-9JBXTHSD]|(V[A-GKMORTV]|K[A-HJ-NORUZ]|H[A-GLOR]|[BCGJLNS][A-DKMNORVY]|G[JS])[0-9]{5}|[0-9]{6}使|([沪粤川渝辽云桂鄂湘陕藏黑]A|闽D|鲁B|蒙[AEH])[0-9]{4}领|粤Z[0-9A-HJ-NP-Z][0-9]{3}[港澳]')",
"REGEXP_MATCH(a1, '1[3-9][[:digit:]]{9}')",
"REGEXP_MATCH(a1, '([1-9]{1})([[:digit:]]{14}|[[:digit:]]{18})')",
"round(a1,2)",
"CHAR_LENGTH(a1)",
" case" +
" when SUBSTRING(a1,18,1)=1 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=0 then 1\n" +
" when SUBSTRING(a1,18,1)=0 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=1 then 1\n" +
" when SUBSTRING(a1,18,1)='X' and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=2 then 1\n" +
" when SUBSTRING(a1,18,1)=9 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=3 then 1\n" +
" when SUBSTRING(a1,18,1)=8 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=4 then 1\n" +
" when SUBSTRING(a1,18,1)=7 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=5 then 1\n" +
" when SUBSTRING(a1,18,1)=6 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=6 then 1\n" +
" when SUBSTRING(a1,18,1)=5 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=7 then 1\n" +
" when SUBSTRING(a1,18,1)=4 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=8 then 1\n" +
" when SUBSTRING(a1,18,1)=3 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=9 then 1\n" +
" when SUBSTRING(a1,18,1)=2 and MOD(SUBSTRING(a1,1,1)*7 + SUBSTRING(a1,2,1)*9 + SUBSTRING(a1,3,1)*10 + SUBSTRING(a1,4,1)*5 + SUBSTRING(a1,5,1)*8 + SUBSTRING(a1,6,1)*4 + SUBSTRING(a1,7,1)*2 + SUBSTRING(a1,8,1)*1 + SUBSTRING(a1,9,1)*6 + SUBSTRING(a1,10,1)*3 + SUBSTRING(a1,11,1)*7 + SUBSTRING(a1,12,1)*9 + SUBSTRING(a1,13,1)*10 + SUBSTRING(a1,14,1)*5 + SUBSTRING(a1,15,1)*8 + SUBSTRING(a1,16,1)*4 + SUBSTRING(a1,17,1)*2,11)=10 then 1\n" +
" else 0 end",
"CASE WHEN a1 regexp '^(京[A-HJ-NPQY]|沪[A-HJ-N]|津[A-HJ-NPQR]|渝[A-DFGHN]|冀[A-HJRST]|晋[A-FHJ-M]|蒙[A-HJKLM]|辽[A-HJ-NP]|吉[A-HJK]|黑[A-HJ-NPR]|苏[A-HJ-N]|浙[A-HJKL]|皖[A-HJ-NP-S]|闽[A-HJK]|赣[A-HJKLMS]|鲁[A-HJ-NP-SUVWY]|豫[A-HJ-NP-SU]|鄂[A-HJ-NP-S]|湘[A-HJ-NSU]|粤[A-HJ-NP-Y]|桂[A-HJ-NPR]|琼[A-F]|川[A-HJ-MQ-Z]|贵[A-HJ]|云[AC-HJ-NP-SV]|藏[A-HJ]|陕[A-HJKV]|甘[A-HJ-NP]|青[A-H]|宁[A-E]|新[A-HJ-NP-S])([0-9A-HJ-NP-Z]{4}[0-9A-HJ-NP-Z挂试]|[0-9]{4}学|[A-D0-9][0-9]{3}警|[DF][0-9A-HJ-NP-Z][0-9]{4}|[0-9]{5}[DF])$|^WJ[京沪津渝冀晋蒙辽吉黑苏浙皖闽赣鲁豫鄂湘粤桂琼川贵云藏陕甘青宁新]?[0-9]{4}[0-9JBXTHSD]$|^(V[A-GKMORTV]|K[A-HJ-NORUZ]|H[A-GLOR]|[BCGJLNS][A-DKMNORVY]|G[JS])[0-9]{5}$|^[0-9]{6}使$|^([沪粤川渝辽云桂鄂湘陕藏黑]A|闽D|鲁B|蒙[AEH])[0-9]{4}领$|^粤Z[0-9A-HJ-NP-Z][0-9]{3}[港澳]$' THEN 1 ELSE 0 END",
"CASE WHEN a1 regexp '^1[3-9][[:digit:]]{9}$' THEN 1 ELSE 0 END",
"REGEXP_MATCH(a1, '[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}|[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)')",
"REGEXP_MATCH(a1, '(京[A-HJ-NPQY]|沪[A-HJ-N]|津[A-HJ-NPQR]|渝[A-DFGHN]|冀[A-HJRST]|晋[A-FHJ-M]|蒙[A-HJKLM]|辽[A-HJ-NP]|吉[A-HJK]|黑[A-HJ-NPR]|苏[A-HJ-N]|浙[A-HJKL]|皖[A-HJ-NP-S]|闽[A-HJK]|赣[A-HJKLMS]|鲁[A-HJ-NP-SUVWY]|豫[A-HJ-NP-SU]|鄂[A-HJ-NP-S]|湘[A-HJ-NSU]|粤[A-HJ-NP-Y]|桂[A-HJ-NPR]|琼[A-F]|川[A-HJ-MQ-Z]|贵[A-HJ]|云[AC-HJ-NP-SV]|藏[A-HJ]|陕[A-HJKV]|甘[A-HJ-NP]|青[A-H]|宁[A-E]|新[A-HJ-NP-S])([0-9A-HJ-NP-Z]{4}[0-9A-HJ-NP-Z挂试]|[0-9]{4}学|[A-D0-9][0-9]{3}警|[DF][0-9A-HJ-NP-Z][0-9]{4}|[0-9]{5}[DF])|WJ[京沪津渝冀晋蒙辽吉黑苏浙皖闽赣鲁豫鄂湘粤桂琼川贵云藏陕甘青宁新]?[0-9]{4}[0-9JBXTHSD]|(V[A-GKMORTV]|K[A-HJ-NORUZ]|H[A-GLOR]|[BCGJLNS][A-DKMNORVY]|G[JS])[0-9]{5}|[0-9]{6}使|([沪粤川渝辽云桂鄂湘陕藏黑]A|闽D|鲁B|蒙[AEH])[0-9]{4}领|粤Z[0-9A-HJ-NP-Z][0-9]{3}[港澳]')",
"REGEXP_MATCH(a1, '1[3-9][[:digit:]]{9}')",
"REGEXP_MATCH(a1, '([1-9]{1})([[:digit:]]{14}|[[:digit:]]{18})')"
);
List<String> whereList=Lists.newArrayList(
"a1='2'",
"(a1='2')",
"((a1='2'))",
"a1='2' and a2='3'",
"(a1='2' and a2='3')",
"((a1='2' and a2='3'))",
"(a1='2') and (a2='3')",
"((a1='2')) and ((a2='3'))",
"((a1=a4)) and ((a2=a3))",
"(a1=a4) and (a2=a3)",
"a1=a4 and a2=a3",
"(a1=a4 and a2=a3)",
"((a1=a4 and a2=a3))",
"a1=a4 or a2=a3",
"a1=a4 or a2=232",
"a1=a4 or a2=232 and a3=12",
"a1=a4 or (a2=232 and a3=12)",
"(a1=a4 or a2=232) and a3=12",
"((a1=a4 or a2=232)) and a3=12"
);
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
