-2

I have string like 

order by o desc,b asc

Here I want to replace o and b columns of this clause by table_o and table_b and output 

order by table_o desc, table_b asc

I am using replace function for that but output becomes like 

table_order table_by table_o desc,table_b asc

How to solve this problem using regular expression?

One more example 

"order by orders desc, bye asc"

should be replaced as 

"order by table_orders desc, table_bye asc"
Prashant Bhate
  • 10,907
  • 7
  • 47
  • 82
SRK
  • 1,020
  • 1
  • 9
  • 11

5 Answers5

2

Here is one possible solution. [You might have to tweak spaces around desc asc and , based on your actual SQL]

    String str = "select a,b,c * from Table order by o desc,b asc,c,d";
    System.out.println(str.replaceAll(
            "(.*order by )?(\\w+)( desc| asc)?(,|$)", "$1table_$2$3$4"));

Result

select a,b,c * from Table order by table_o desc,table_b asc,table_c,table_d

Visual Regex (.*order by )?(\w+)( desc| asc)?(,|$)

Regex details

  • (.*order by)? => will match select a,b,c * from Table order by =>back ref $1
  • (\\w+)               => will match column name =>back ref $2
  • ( desc| asc)? => will match desc or asc => back ref $3
  • (,|$)                 => will match trailing comma or endof line => back ref $4

Please Note : this solution only works with simple sql queries, and would produce wrong result if the order byclause is part of inner query of a complex SQL. Moreover Regex is not can not ideal tool to parse SQL syntax

See this link Regular expression to match common SQL syntax?

   If full-fledged SQL parsing is required, Its better to use either SQL parsers or Parser generators like ANTLR to parse SQL. See this link for list of available ANTLR SQL grammer

Prashant Bhate
  • 10,907
  • 7
  • 47
  • 82
  • thanks...its working.. Now if instead of replacing with table_ , suppose there is a string variable str which contains string to be replaced then how to write? – SRK Jan 23 '12 at 10:28
  • @SRK Try this `"$1" + tableStr +"$2$3$4"` where `tableStr` is your variable that contains replacement string – Prashant Bhate Jan 23 '12 at 10:36
  • yes you are right your solution is not working for even query like "select * from a where a>=5 and b<6 order by a,b,c;" so what do you suggest I should not use regular expression? – SRK Jan 23 '12 at 10:50
  • Though it would work for your query if you change it to `(,|$|;$)` in above regex, general solution is to make this replacement while Constructing SQL Query. If this is not an option you may need to consider using SQL parsers or Parser Generators like http://www.antlr.org to parse SQL – Prashant Bhate Jan 23 '12 at 11:01
0

Simply use a space in the replace function (you do not need a regex). Pseudo-code:

string = string_replace(string, " o ", " table_o ")

Edit:

After your example, you can but every valid boundary between [ and ]. The regex will then match is. To get back the origional boundary put it between ( and ) and replace it back.

E.g.:

string = regex_replace(string, "([ \t])o([ \t,])", "\1o\2")

\1 and \2 might be different in your regex implementation. Also I'd suggest clarifying your case so that it is clear what you really want to replace and also take a look at Truth's suggestion of the XY problem.

dtech
  • 13,741
  • 11
  • 48
  • 73
  • I tried using space itself...but there are some cases in which it wont work and thats why I am asking for regex. – SRK Jan 23 '12 at 09:53
  • Well then give the cases in which it won't work. The space is the solution to the current problem you're stating so you'll have to further define it! Also see Truth's very true comment about the XY problem. – dtech Jan 23 '12 at 09:55
  • you didnt get it... suppose my string is " order by ord," then using str.replace(" ord ") wont work because there is comma after it... – SRK Jan 23 '12 at 10:00
  • Well, you didn't mention ANYTHING about comma's possibly beging after the thing you were trying to replace. That is why I said: give the correct examples... – dtech Jan 23 '12 at 10:02
  • I think you need to let us help you instead of thinking you are right and the rest of the world doesn't get it – dtech Jan 23 '12 at 10:07
0

If you just want to replace text like that just use these regexes:

" o "

" b "
annonymously
  • 4,708
  • 6
  • 33
  • 47
  • I tried using space itself...but there are some cases in which it wont work and thats why I am asking for regex. – SRK Jan 23 '12 at 09:54
  • What cases? you'll need to give a more exact example if you want us to be able to help you. – annonymously Jan 23 '12 at 09:56
  • you didnt get it... suppose my string is " order by ord," then using str.replace(" ord ") wont work because there is comma after it.. – SRK Jan 23 '12 at 10:01
  • ah, well then you can use `" ord[ ,.;:]"` (add any other characters you think are appropriate) – annonymously Jan 23 '12 at 10:04
0

Probably you are looking for this? Regular Expressions in Java SE & EE
Have a look at Regular Expressions chapter that will do the work most of the times.

Kounavi
  • 1,090
  • 1
  • 12
  • 24
0

You can use code like this to convert your text:

String sql = "select o, b, c,d form Table order by orders ,b asc, c desc,d desc, e";
String text = sql.toLowerCase();
String orderBy = "order by ";
int start = text.indexOf(orderBy);
if (start >= 0) {
    String subtext = text.substring(start+orderBy.length());
    System.out.printf("Replaceed: [%s%s%s]%n", text.substring(0, start), orderBy, subtext.replaceAll("(\\w+)(\\s+(?:asc|desc)?,?\\s*)?", "table_$1$2"));
}

OUTPUT:

Replaceed: [select o, b, c,d form table order by table_orders ,table_b asc, table_c desc,table_d desc, table_e]
anubhava
  • 761,203
  • 64
  • 569
  • 643