1

I have a string 

String SQL = "SELECT POSITION('Bengal' IN STRING) from delivery";

I want to replace the POSITION() function from the SQL string with this String INSTR(STRING,'Bengal'). And in the POSITION function of SQL String 'Bengal' and STRING is not a fixed values it will change as per user requirement but POSITION,the open braces "(",IN and closing braces ")" is static. The final out put has to be look like this using Regular Expression.

String SQL ="SELECT INSTR(STRING,'Bengal') from delivery";

So in the string if it repeats two times then it has to replace two time with corresponding arguments into the same string SQL. Any help will be appreciated.

zameer
  • 471
  • 1
  • 5
  • 15

1 Answers1

2

In Java, use this Regex:

SQL = SQL.replaceAll("POSITION\\((.*?) IN ([A-Z]+)\\)", "INSTR($2, $1)");

Note, however, that it will work only for simple cases of the input String, if the first parameter is some expression and second is something more complex, it may not work. It will even work for multiple POSITION expressions in the query, such as this:

String SQL = "SELECT POSITION('Bengal''s something IN d' IN STRING), POSITION('text' IN STRINGX) from delivery";
SQL = SQL.replaceAll("POSITION\\((.*?) IN ([A-Z]+)\\)", "INSTR($2, $1)");
// SQL now contains: "String SQL = "SELECT INSTR(STRING, 'Bengal''s something IN d'), INSTR(STRINGX, 'text') from delivery";"

If you want to completely correctly replace the function call, use proper sqlparser. In the past I used JSQLParser. It can handle even subselects as POSITION parameters with nested POSITION calls.

Oliv
  • 10,221
  • 3
  • 55
  • 76
  • :Thanks for the post but the out put is different rather than what i expected, the out put is :SELECT INSTR(2, 1), INSTR(2, 1) from delivery.But i want it like this SELECT INSTR(STRINGX, 'text'), INSTR(STRINGX, 'text') from delivery. – zameer Jan 14 '16 at 09:17
  • When i used the above directly inside the java code its working fine but when i add the pattern inside mysql data base,its not giving the expected output,its giving the same output as my input. – zameer Jan 14 '16 at 10:51
  • How do you put it to mysql database? You should use regexp package, look here: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql. I'm not an MySQL expert, your question is tagged `java`. – Oliv Jan 15 '16 at 09:14