这段代码估计用不到了，写的很简单，用了最直观的思路替换参数。
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class 替换字符串参数_S4_Test {
public static void main(String[] args) throws Exception {
String str="select rownum, bb.* from (select :a, :b + 4, 4 + :c, 5 + :b, to_char(:c, 'yyy-mm-dd'), to_date(:v, 'yyyy-mm-dd') from a where a.order_id = :b and b in (:b) and c in (:b, :l) and d in (:l, 0) and e in (0, :l) and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd') and b.prod_spec_id = :MODEL_ID and extract(month from b.done_date) = extract(month from to_date(:b, ' yyyy - mm ')) and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date) and a.order_type = 80 and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd') and extract(month from b.done_date) = extract(month from to_date(:b, 'yyyy-mm')) and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date) and extract(month from b.done_date) = extract(month from to_date(:b, ' yyyy - mm ')) union select extract(month from to_date(:b, ' yyyy - mm ')), to_char(:v, 'yyy-m-dd'), to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd') from b group by a having extract(month from b.done_date) = :b and b = :b union all select extract(month from to_date(:b, ' yyyy - mm ')), to_char(:v, 'yyy-m-dd'), to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd') from b group by a having(extract(month from b.done_date) = :b and b = :b) and b = :k) bb group by aa, bb desc having(aa = :k and bb > :b + 5) ";
str = addSqlBlank(str);
List<String> sqlList = splitSqlByUnion(str);
str=replaceSqlNullValue(sqlList);
System.out.println("替换结果为="+str);
}
public static String replaceSqlNullValue(List<String> sqlList)throws Exception {
if(sqlList==null||sqlList.size()==0){
return "";
}
StringBuffer sb2=new StringBuffer();
for (String sql : sqlList) {
// 取真实的from 是否带where 是否带having
int existsIndex = sql.lastIndexOf(" exists(");
int havingIndex = sql.indexOf(" having");
int fromIndex = sql.lastIndexOf(" from ");
int whereIndex = sql.indexOf(" where ");
int extractIndex = sql.lastIndexOf(" extract(");
String selectSqlStr = null;
if (havingIndex != -1 && fromIndex > havingIndex) {
fromIndex = sql.substring(0, havingIndex).lastIndexOf(" from ");
}
if (whereIndex != -1 && extractIndex != -1) {
fromIndex = sql.substring(0, whereIndex).lastIndexOf(" from ");
}
if (whereIndex != -1 && fromIndex > existsIndex) {
while (existsIndex != -1 && fromIndex > existsIndex) {
String tmpStr = sql.substring(0, existsIndex);
fromIndex = tmpStr.lastIndexOf(" from ");
existsIndex = tmpStr.lastIndexOf(" exists(");
}
}
selectSqlStr = sql.substring(0, fromIndex + 6);
sql = sql.substring(fromIndex + 6);
sb2.append(replaceNullValueAfterSelect(selectSqlStr));
sb2.append(removeAllErrorConnect(replaceValueAfterFrom(sql)));
}
return sb2.toString();
}
public static String replaceNullValueAfterSelect(String sql) {
if (sql.indexOf(":") == -1) {
return sql;
}
int position = sql.indexOf(":");
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
int beforeBracket = 0;// 匹配括号数量
boolean isConnect = false;
char bracketBeforeChar = '\0', bracketAfterChar = '\0';
//直接拿到字符串
while (position != -1) {
// 向←直到空格或者开始
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
if (sql.charAt(i) == ',' || sql.charAt(i) == ' ') {
break;
} else if (sql.charAt(i) == '+' || sql.charAt(i) == '-') {
isConnect = true;
before.append(sql.charAt(i));
bracketBeforeChar = sql.charAt(i);
break;
} else {
before.append(sql.charAt(i));
}
}
// 向→直到空格或者最后
for (int i = position; i < sql.length(); i++) {
if (beforeBracket != 0) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
after.append(sql.charAt(i));
continue;
}
if (sql.charAt(i) == '+' || sql.charAt(i) == '-') {
bracketAfterChar = sql.charAt(i);
after.append(sql.charAt(i));
isConnect = true;
break;
}
if (sql.charAt(i) != ' ' && sql.charAt(i) != ',') {
after.append(sql.charAt(i));
} else {
break;
}
}
after.insert(0, before.reverse());
if (isConnect) {
sql = sql.replace(
after.toString(),
String.valueOf(
bracketBeforeChar + "'0'" + bracketAfterChar)
.replace('\0', ' '));
} else {
sql = sql.replace(after.toString(), "' '");
}
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
beforeBracket = 0;
isConnect = false;
bracketAfterChar = '\0';
bracketBeforeChar = '\0';
}
after = null;
before = null;
sql = sql.replaceAll("(extract\\s*\\()([^)]*'\\s+')(\\s*\\))", "' '");;
return new String(sql);
}
public static String replaceValueAfterFrom(String sql) throws Exception {
if (sql.indexOf(":") == -1) {
return sql;
}
StringBuffer result = new StringBuffer();
//含连接符
String[] tmpStrArr = splitByStrNormal(sql, "(?<=\\s+)(and|or)(?=\\s+|\\s*\\()");
for (String subStr : tmpStrArr) {
if (subStr.indexOf(":") == -1) {
if (subStr.replaceAll("(?<=\\s+|^)(and|or)(?=\\s+|\\)|$)", "").trim().length() == 0) {
result.append(' ');
} else {
result.append(subStr);
}
} else if (subStr.indexOf(" in ") != -1) {
result.append(replaceNullParaWithIn(subStr));
} else {
result.append(replaceParaNormalWithBlank(subStr));
}
}
return result.toString();
}
public static String replaceParaNormalWithBlank(String sql) throws Exception {
int position = sql.indexOf(":");
if (position == -1) {
return sql;
}
// 忽略extract
if (sql.indexOf("extract") != -1) {
return removeExtract(sql);
}
if (sql.indexOf("to_date") == -1 && sql.indexOf("to_char") == -1) {
if (sql.indexOf("+") != -1 || sql.indexOf("-") != -1) {
return replaceParaWithConnect(sql);
}
}
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
int leftBracket =0, rightBracket =0;
// 括号匹配
int beforeBracket = 0;// 匹配括号数量
int leftIndex=0,rightIndex=sql.length();
while (position != -1) {
leftBracket =Math.abs(sql.substring(0, position).replaceAll("\\(", "").length()-sql.substring(0, position).replaceAll("\\)", "").length());
rightBracket =Math.abs(sql.substring(position).replaceAll("\\(", "").length()-sql.substring(position).replaceAll("\\)", "").length());
if(leftBracket>rightBracket){
for (int i = 0; i < position; i++) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
if(beforeBracket==leftBracket-rightBracket){
leftIndex=i+1;
break;
}
}
}else if(leftBracket<rightBracket){
for(int i=sql.length()-1;i>position;i--){
if (sql.charAt(i) == '(') {
beforeBracket--;
} else if (sql.charAt(i) == ')') {
beforeBracket++;
}
if(beforeBracket==rightBracket-leftBracket){
rightIndex=i;
break;
}
}
}
// 向←直到空格或者开始
for (int i = position - 1; i >=leftIndex; i--) {
if (sql.charAt(i) != ' ') {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到空格或者最后
for (int i = position; i <rightIndex; i++) {
if (beforeBracket != 0) {
if (sql.charAt(i) == '(') {
beforeBracket++;
} else if (sql.charAt(i) == ')') {
beforeBracket--;
}
after.append(sql.charAt(i));
continue;
}
if (sql.charAt(i) != ' ' && sql.charAt(i) != ')') {
after.append(sql.charAt(i));
} else {
break;
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "");
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
beforeBracket = 0;
leftIndex=0;
rightIndex=sql.length();
}
before.setLength(0);
after.setLength(0);
after = null;
before = null;
if(sql.indexOf("(")==-1&&sql.indexOf(")")==-1){
return sql.replaceAll("(?<=\\s+|^)(and|or)(?=\\s+|\\)|$)", "");
}
return sql;
}
public static String removeExtract(String sql) throws Exception {
int beforeBracket=0;
int position=sql.indexOf(":");
String subStr=sql.substring(0,position);
int whereIndex=subStr.lastIndexOf(" where ");
int havingIndex=subStr.lastIndexOf(" having ");
int havingIndex2=subStr.lastIndexOf(" having( ");
StringBuffer result=new StringBuffer();
//删除extract
if(whereIndex!=-1){
result.append(sql.substring(0, whereIndex+" where ".length()));
sql=sql.substring(whereIndex+" where ".length());
}else if(havingIndex!=-1){
result.append(sql.substring(0, havingIndex+" having ".length()));
sql=sql.substring(havingIndex+" having ".length());
}else if(havingIndex2!=-1){
result.append(sql.substring(0, havingIndex2+" having(".length()));
sql=sql.substring(havingIndex2+" having(".length());
}
boolean paramFlag=false,bracketEnd=false;
for(int i=Math.max(0,havingIndex2),len=sql.length();i<len;i++){
if(sql.charAt(i)=='('){
beforeBracket++;
bracketEnd=true;
continue;
}else if(sql.charAt(i)==')'){
beforeBracket--;
continue;
}
if(sql.charAt(i)==':'){
paramFlag=true;
}
if(bracketEnd&¶mFlag&&beforeBracket==0){
if(sql.charAt(i)==' '||sql.charAt(i)==')'){
result.append(sql.substring(i));
break;
}
}
}
return replaceNullParaWithIn(result.toString());
}
// 含<>的连接符的变量替换为0
public static String replaceParaWithConnect(String sql) {
int position = sql.indexOf(":");
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
while (position != -1) {
if (sql.substring(0, position).indexOf(">") == -1
&&sql.substring(0, position).indexOf("<") == -1
&&sql.substring(0, position).indexOf("!=") == -1) {
// 向←直到连接符
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) != '(') {
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到连接符或者最后
for (int i = position; i < sql.length(); i++) {
if (sql.charAt(i) == ' ' || sql.charAt(i) == ')') {
break;
} else {
after.append(sql.charAt(i));
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "");
} else {
// 向←直到连接符
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) != '=' && sql.charAt(i) != '<'
&& sql.charAt(i) != '>' && sql.charAt(i) != '+'
&& sql.charAt(i) != '-') {
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到连接符或者最后
for (int i = position; i < sql.length(); i++) {
if (sql.charAt(i) == '+' || sql.charAt(i) == '-'
|| sql.charAt(i) == ')') {
break;
} else {
after.append(sql.charAt(i));
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "'0'");
}
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
}
before.setLength(0);
after.setLength(0);
after = null;
before = null;
sql=sql.replaceAll("(\\(\\s*)(and|or)(\\s+)", "( ");
sql=sql.replaceAll("(\\s*)(and|or)(\\s*\\(\\s*\\))", " ");
return new String(sql);
}
// 替换in括号内的参数
public static String replaceNullParaWithIn(String sql) throws Exception {
int position = sql.indexOf(":");
if (position == -1 || sql.indexOf("in") == -1) {
return removeInErrorSem(sql);
}
StringBuffer before = new StringBuffer();
StringBuffer after = new StringBuffer();
while (position != -1) {
// 向←直到空格或者开始
for (int i = position - 1; i >= 0; i--) {
if (sql.charAt(i) != ' ' && sql.charAt(i) != ',') {
before.append(sql.charAt(i));
} else {
break;
}
}
// 向→直到空格或者最后
for (int i = position; i < sql.length(); i++) {
if (sql.charAt(i) != ' ' && sql.charAt(i) != ','
&& sql.charAt(i) != ')') {
after.append(sql.charAt(i));
} else {
break;
}
}
after.insert(0, before.reverse());
sql = sql.replace(after.toString(), "");
position = sql.indexOf(":");
before.setLength(0);
after.setLength(0);
}
before.setLength(0);
after.setLength(0);
after = null;
before = null;
sql = removeInErrorSem(sql);
sql = sql.replaceAll(",\\s*\\)", ")");
sql = sql.replaceAll("in\\s*\\(\\s*\\)", "in()");
sql = removeInBlankBracket(sql);
return new String(sql);
}
// 清除in括号内多余的逗号
public static String removeInErrorSem(String str) throws Exception {
int position = str.indexOf(" in");
if (position == -1) {
return str;
}
StringBuffer sb = new StringBuffer();
boolean isStart = false;
int endIndex = str.length();
while (position != -1) {
sb.append(str.substring(0, position + 3));
for (int i = position + 3; i < str.length(); i++) {
if (str.charAt(i) == '(') {
sb.append(str.charAt(i));
continue;
}
if (str.charAt(i) == ')') {
sb.append(str.charAt(i));
endIndex = i;
break;
}
if (str.charAt(i) != ',') {
sb.append(str.charAt(i));
if (str.charAt(i) != ' ') {
isStart = true;
}
} else if (isStart && str.charAt(i) == ',') {
sb.append(str.charAt(i));
isStart = false;
}
}
str = str.substring(endIndex + 1);
endIndex = str.length();
position = str.indexOf(" in");
}
if (str != null) {
sb.append(str);
}
str = sb.toString();
sb.setLength(0);
sb = null;
return new String(str);
}
// 清除in空括号
public static String removeInBlankBracket(String sql) {
if (sql.indexOf("in()") == -1) {
return sql;
}
int position = sql.indexOf("in()");
StringBuffer sb = new StringBuffer();
int startIndex = 0;
boolean isParam = false, isChange = false;
;
while (position != -1) {
for (int i = position - 1; i >= 0; i--) {
if (!isParam) {
if (sql.charAt(i) != ' ') {
isParam = true;
continue;
}
}
if (isParam) {
if (sql.charAt(i) == '(') {
isParam = false;
startIndex = i + 1;
isChange = true;
break;
} else if (i >= 2 && sql.charAt(i) == 'd'
&& sql.charAt(i - 1) == 'n'
&& sql.charAt(i - 2) == 'a') {
isParam = false;
isChange = true;
startIndex = i - 2;
break;
} else if (i >= 1 && sql.charAt(i) == 'r'
&& sql.charAt(i - 1) == 'o') {
isParam = false;
isChange = true;
startIndex = i - 1;
break;
}
}
}
if (isChange) {
sb.append(sql.substring(0, startIndex));
} else {
sb.append(sql.substring(0, position + 4));
}
startIndex = 0;
isParam = false;
isChange = false;
sql = sql.substring(position + 4);
position = sql.indexOf("in()");
}
if (sql != null) {
sb.append(sql);
}
sql = sb.toString();
sb.setLength(0);
sb = null;
sql = sql.replaceAll("(\\(\\s*)(and|or)(\\s+)", "( ");
sql = sql.replaceAll("(\\s*|^)(and|or)(\\s*\\(\\s*\\))", " ");
return new String(sql);
}
public static String removeAllErrorConnect(String sql) throws Exception {
sql = replaceSqlWithRegex(sql,"(\\(\\s*)(and|or)(\\s*\\)?)", new int[]{1,3});
sql = replaceSqlWithRegex(sql,"(\\s+)(and|or)(\\s*\\))", new int[]{1,3});
sql = replaceSqlWithRegex(sql,"(\\s+and|or)(\\(\\s*,?\\s*\\))(\\s*,?$?)", new int[]{3});// 清空空括号
sql = replaceSqlWithRegex(sql, "(\\s*)(\\(\\s*,?\\s*\\))(\\s*,?$?)", new int[]{3});// 清空空括号
sql = replaceSqlWithRegex(sql, "(where\\s+)(and|or|in)(\\s+|$)", new int[]{1});
sql = replaceSqlWithRegex(sql,"(\\s+)(where|having|order\\s+by|group\\s+by)(\\s*$|\\s*\\)|\\s+union)",new int[]{3});
sql = replaceSqlWithRegex(sql, "where\\s+(group|order|$)", new int[]{1});
sql = replaceSqlWithRegex(sql, "having\\s+(order|$)", new int[]{1});
sql = replaceSqlWithRegex(sql, "group\\s+by\\s+(order|$)", new int[]{1});
sql = sql.replaceAll("\\s{2,}", " ");// 删除多余空格
return new String(sql);
}
// sql添加空格
public static String addSqlBlank(String sql) throws Exception {
sql = replaceSqlWithRegex(sql, "(\\s+)(=|<|>|!|-|\\+|\\*|/|\\()(\\s+)",new int[]{2});// 去除运算符附近空格
sql = replaceSqlWithRegex(sql, "(\\s+)(=|<|>|!|-|\\+|\\*|/)(\\s*)", new int[]{2});// 去除运算符附近空格
sql = replaceSqlWithRegex(sql, "(\\s*)(=|<|>|!|-|\\+|\\*|/)(\\s+)", new int[]{2});// 去除运算符附近空格
sql = replaceSqlWithRegex(sql, "(where\\s+)(,|and|or|in)(\\s+|$)", new int[]{1});// 清空where后面直接带连接符
sql = cleanSqlComment(sql);
sql = lowerCaseOracleKeyWord(sql);
sql = sql.replaceAll("\\s*:\\s*", ":");// 去除:附近空格
sql = sql.replaceAll("\\*from", "* from ");// from前面添加空格
sql = sql.replaceAll("select", "select ");// from前面添加空格
sql = sql.replaceAll("\\)", " )");// )前面添加空格
sql = sql.replaceAll("(?<!and|or)\\s*\\(\\s*", "(");// 去除(附近空格
sql = sql.replaceAll("\\s+in\\s*\\(", " in ( ");// in(添加空格
sql = sql.replaceAll("\\s+having\\s*\\(", " having( ");// having(添加空格
sql = sql.replaceAll(",", " , ");// ,后面添加空格
// 删除不匹配的注释
sql = sql.replace("/*", " ");
sql = sql.replace("*/", " ");
return new String(sql);
}
public static String replaceSqlWithRegex(String sql, String regex,
int[] indexs) throws Exception {
Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
Arrays.sort(indexs);
// 添加循环匹配失败检测
int isFail = 0;
while (true) {
while (m.find()) {
isFail++;
if (isFail > Byte.MAX_VALUE) {
throw new Exception("检测到匹配次数过多，请优化匹配表达式");
}
String tmp = new String();
for (int i = 0; i < indexs.length; i++) {
tmp += m.group(indexs[i]);
}
sql = sql.replace(m.group(), tmp);
}
m = p.matcher(sql);
if (!m.find()) {
break;
}
m.reset();
}
return new String(sql);
}
// 数据库关键字小写
public static String lowerCaseOracleKeyWord(String sql) throws Exception {
String regexStr = "(?<=\\s+|\\(|\\)|^)(select|as|from|where|and|or|in|exists|extract|to_date|to_char|not|trunc|group|by|having|order)(?=\\s+|\\(|\\)|$)";
sql = replaceStrByAppend(regexStr, sql, true);
return sql;
}
// 大小写字符串
public static String replaceStrByAppend(String regEx, String sql,
boolean isLower) {
Pattern pattern = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(sql);
StringBuffer sb = new StringBuffer();
int lastEnd = 0;// 上一次匹配结束位置
while (matcher.find()) {
sb.append(sql.substring(lastEnd, matcher.start()));
if (isLower) {
sb.append(sql.substring(matcher.start(), matcher.end())
.toLowerCase());
} else {
sb.append(sql.substring(matcher.start(), matcher.end()));
}
lastEnd = matcher.end();
}
if (lastEnd < sql.length()) {
sb.append(sql.substring(lastEnd));
}
return sb.toString();
}
public static List<String> splitSqlByUnion(String sql){
List<String> resultList=new ArrayList<String>();
if(sql.indexOf(" union ")==-1){
resultList.add(sql);
}else{
int position=sql.indexOf(" union ");
while(position!=-1){
String tmpStr=sql.substring(0,sql.indexOf(" union ")+7);
resultList.add(tmpStr);
sql=sql.substring(sql.indexOf(" union ")+7);
position=sql.indexOf(" union ");
}
if(sql!=null){
resultList.add(sql);
}
}
return resultList;
}
// 字符串分割保留分隔符
public static String[] splitByStrNormal(String str, String split) {
Pattern p = Pattern.compile(split);
Matcher m = p.matcher(str);
String[] words = p.split(str);
String[] strResult = new String[words.length];
strResult[0] = words[0];
if (words.length > 0) {
int count = 1;
while (count < words.length) {
if (m.find()) {
strResult[count] = new String(m.group() + words[count]);
}
count++;
}
}
return strResult;
}
// 清除sql内的注释
public static String cleanSqlComment(String sql) {
if (sql.indexOf("/*") == -1) {
return sql;
}
StringBuilder sb = new StringBuilder((int) (sql.length() * 1.5));
int cursor = 0, start = -1, end = -1;
for (; (start = sql.indexOf("/*", cursor)) != -1
&& (end = sql.indexOf("*/", start)) != -1;) {
sb.append(' ').append(sql.substring(cursor, start));
cursor = end + 2;
}
sb.append(sql.substring(cursor, sql.length()));
return sb.toString();
}
// 初步检查sql是否正确 规则1)select前面有其他字符,2)sql括号数量不匹配 3)sql中注释不匹配
public static String checkSql(String sql) {
StringBuffer errorInfo = new StringBuffer();
sql = sql.trim().toLowerCase();
if (!sql.startsWith("select")) {
errorInfo.append(" select开始前有其他字符");
}
int leftBracket = sql.length() - sql.replaceAll("\\(", "").length(), rightBracket = sql
.length() - sql.replaceAll("\\)", "").length();
if (leftBracket != rightBracket) {
errorInfo.append(" 括号不匹配");
}
sql = cleanSqlComment(sql);
if (sql.indexOf("/*") != -1 || sql.indexOf("*/") != -1) {
errorInfo.append(" sql中存在多余的注释");
}
return errorInfo.toString();
}
public static StringBuffer map2StringBuffer(Map<String, String> map) {
StringBuffer result = new StringBuffer();
for (Entry<String, String> entry : map.entrySet()) {
result.append(entry.getKey()).append("=").append(entry.getValue())
.append(",");
}
if (result.length() > 0) {
result.deleteCharAt(result.length() - 1);
}
return result;
}
}
替换前:
select rownum, bb.*
from (select :a,
:b + 4,
4 + :c,
5 + :b,
to_char(:c, 'yyy-mm-dd'),
to_date(:v, 'yyyy-mm-dd')
from a
where a.order_id = :b
and b in (:b)
and c in (:b, :l)
and d in (:l, 0)
and e in (0, :l)
and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd')
and b.prod_spec_id = :MODEL_ID
and extract(month from b.done_date) =
extract(month from to_date(:b, ' yyyy - mm '))
and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date)
and a.order_type = 80
and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd')
and extract(month from b.done_date) =
extract(month from to_date(:b, 'yyyy-mm'))
and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date)
and extract(month from b.done_date) =
extract(month from to_date(:b, ' yyyy - mm '))
union
select extract(month from to_date(:b, ' yyyy - mm ')),
to_char(:v, 'yyy-m-dd'),
to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd')
from b
group by a
having extract(month
from b.done_date) = :b and b = :b
union all
select extract(month from to_date(:b, ' yyyy - mm ')),
to_char(:v, 'yyy-m-dd'),
to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd')
from b
group by a
having(extract(month
from b.done_date) = :b and b = :b) and b = :k) bb
group by aa, bb desc
having(aa = :k and bb > :b + 5)
替换后:
替换结果为=select rownum , bb.* from(select ' ' , '0'+4 , 4+'0' , 5+'0' , ' ' , ' ' from a where d in ( 0 ) and e in ( 0 ) and a.order_type=80 union select ' ' , ' ' , ' ' from b group by a union all select ' ' , ' ' , ' ' from b group by a ) bb group by aa , bb desc having( bb>'0'+5 )
select rownum, bb.*
from (select ' ', '0' + 4, 4 + '0', 5 + '0', ' ', ' '
from a
where d in (0)
and e in (0)
and a.order_type = 80
union
select ' ', ' ', ' '
from b
group by a
union all
select ' ', ' ', ' ' from b group by a) bb
group by aa, bb desc
having(bb > '0' + 5)
全文完。
相关推荐
- **应用场景**：此类函数可以广泛应用于文本处理、数据清洗等场景，尤其在需要批量替换字符串的情况下非常有用。 - **注意事项**： - 在使用过程中需要注意输入参数的有效性，避免出现空字符串或非法字符等情况。 ...
在这个案例中，我们要关注的是如何替换字符串中的反斜杠字符`\`。 ### 1. `replaceAll`函数介绍 `replaceAll`函数的基本语法是： ```java public String replaceAll(String regex, String replacement) ``` 参数...
命令行下替换字符串，用于英文替换，中文替换会出错
Java字符串中${}或者{}等占位符替换工具类 Java字符串中${}或者{}等占位符替换工具类是一个功能强大且实用的工具类，它可以将Java字符串中的占位符依次替换为指定的值。该工具类的主要功能是实现占位符的替换，即将...
另外，如果你需要在文件系统或数据库中批量替换字符串，那么操作流程会更复杂。可能需要读取文件内容到内存，进行替换操作，然后再写回文件；如果是数据库，可能涉及SQL查询和更新语句。例如，使用`File....
在这个例子中，`replaceString()`函数接收三个参数：原字符串引用，需要替换的子串，以及替换后的子串。函数通过不断查找并替换找到的子串来完成替换操作。注意，由于`find()`可能会在已替换的区域内找到新的匹配项...
在这个例子中，我们定义了一个名为`resetBlank`的函数，它接受一个字符串参数`str`。函数内部，我们使用了正则表达式`\s+`和`replace()`方法来替换字符串中的多个空格。需要注意的是，我们使用了全局搜索标志`g`来...
在C#编程中，查找和替换字符串是常见的操作，尤其在...总之，C#提供了丰富的字符串处理功能，使得在程序中查找和替换字符串变得简单。通过合理选择和组合这些方法，开发者可以根据需求创建高效且灵活的文本处理逻辑。
在IT行业中，批量替换字符串是一项常见的任务，尤其在文本处理、代码编辑或数据清理时。这个操作涉及到遍历指定文件夹及其子文件夹中的所有指定类型的文件，并将这些文件内的特定字符串替换为新的字符串。标题“批量...
下面是一个简单的Python脚本示例，展示如何实现批量替换字符串的功能： ```python import os def replace_string_in_files(directory, file_extension, old_string, new_string): for root, dirs, files in os....
批量字符串替换工具是一款专为处理此类问题设计的实用工具，其核心功能在于能够快速准确地在文件中定位并替换指定的字符串，无论是简单的文本文件还是复杂的数据文件，它都能应对自如。它最大的亮点在于支持任意文件...
在这个模板中，我们定义了一个名为`StringReplace`的模板，它接受三个参数：`SrcString`（待替换的原始字符串）、`FromString`（需要被替换的子字符串）和`ToString`（替换后的子字符串）。通过递归地调用自身，模板...
// 替换字符串 writer.write(replacedLine); writer.newLine(); // 添加换行符 } writer.close(); reader.close(); } else if (source.isDirectory()) { // 如果是目录 File[] files = source.listFiles(); ...
替换字符串通常涉及到`str.replace()`方法，它会将字符串中的某个子串替换为另一个字符串。在Python中，`str.replace(old, new[, count])`接受三个参数，old是要被替换的子串，new是替换后的字符串，count是可选的，...
本文主要探讨如何使用数组依次替换字符串中的匹配项，这在处理多个相似替换需求时非常有用。 首先，我们看一个例子，假设有一个SQL查询，其中包含需要替换的日期占位符： ```sql select * from table where ctime ...
一个简单的实现可能是遍历字符串，检查每个字符是否与目标字符匹配，如果匹配则替换并继续查找。 3. **字符串比较（strcmp or custom function）**： `strcmp`函数用于比较两个字符串，返回值告诉我们它们是否相等...
`Replace()` 函数则用于替换字符串中的特定子串，它接受四个参数：原始字符串、要查找的子串、替换的新字符串以及可选的替换范围。 一个简单的查找和替换功能的代码可能如下： ```vb Private Sub Command1_Click()...
- `SubstringCount` 函数接收两个字符串参数 `str` 和 `substring`。 - 如果任一参数为空或为 `null`，则返回 0。 - 使用 `Contains` 方法检查 `substring` 是否存在于 `str` 中。 2. **计算子字符串出现次数**...
如果需要在文本输入中实时替换字符串，可以考虑读取标准输入，使用`fgets()`函数获取用户输入，然后调用`str_replace()`处理。 总结来说，C语言实现的字符串替换函数在Linux环境下提供了对文本字符串的便捷操作。...
本篇文章将深入探讨如何使用正则表达式来替换字符串中的内容。 首先，我们来看一个基本的替换示例。在JavaScript中，我们可以使用`String.prototype.replace()`方法来替换字符串中的特定部分。这个方法接受两个参数...