What will be the java code to make an SQL stored as String in multi line?

Question

I have an SQL which is stored in a String goes like

String s="CREATE TABLE BOOK (page number(20, 0), author varchar2(255), noOfLines number(100))";

I need to write a code that could format my sql as

CREATE TABLE BOOK (page number(20, 0),
                    author varchar2(255),
                    noOfLines number(100) 
                  )

What will be the java program to do it.

I tried

sql.replace(", ",", \n");

but this code produced an output like

 CREATE TABLE BOOK (page number(20,
  0),
  author varchar2(255),
  noOfLines number(100) 
 )

As you can see the comma at the number(20, 0) was also considered, but I don't want that.

How to do this?

Possible duplicate: https://stackoverflow.com/questions/312552/looking-for-an-embeddable-sql-beautifier-or-reformatter — drodil, Jul 31 '20 at 09:09
I am looking for simple string operation using regex, pattern ,matcher etc. I believe it is possible. — JITHIN_PATHROSE, Jul 31 '20 at 09:16
No, it isn’t. SQL has a syntax that allows nested expressions, which regex patterns can not handle. You can make it work for your particular example, but, of course, you can also solve a single example by writing the hardcoded intended result. But there is no general solution with just simple pattern matching. — Holger, Jul 31 '20 at 09:46

Attila jáger · Accepted Answer · 2020-07-31T12:21:47.433

3

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class test {
    public static void main(String[] args) {
        final String regex = "[)P],?";
        final String string = "CREATE TABLE BOOK (page number(20, 0), author varchar2(255), noOfLines number(100))";
        final String subst = "$0\\\n                  ";

        final Pattern pattern = Pattern.compile(regex, Pattern.MULTILINE);
        final Matcher matcher = pattern.matcher(string);

// The substituted value will be contained in the result variable
        final String result = matcher.replaceAll(subst);

        System.out.println(result);
    }
}

I made it with link pretty useful page you can even export to other languages like java, python, php ... Hope it helps and I was able to achieve your desired output.

edited Jul 31 '20 at 12:21

answered Jul 31 '20 at 10:00

Attila jáger

86
4

Oh yeah it works with the input you given but if that changes is will most probably not give you the result you want. As @Holger mentioned. – Attila jáger Jul 31 '20 at 10:04
Thanks for this answer!! It worked like magic. A small problem that I encountered was that if there is an column type TIMESTAMP, it won't add new line there. – JITHIN_PATHROSE Jul 31 '20 at 10:17
1

You can change the regex to: final String regex = "[)|P],?"; This will work with even TIMESTAMP. – Attila jáger Jul 31 '20 at 10:28
2

`[)|P]` will match a `)`, a `|`, or an uppercase `P`. What’s the rationale behind it? In which scenarios do you encounter a `|` or upper case `P` before the comma? And isn’t SQL case insensitive? And why do you specify `Pattern.MULTILINE`? Your pattern has no anchors that were affected by the flag. You also don’t need to deal with the backend API for a single translation, `String result = string.replaceAll("\\),?", "$0\n ");` would do the job. – Holger Jul 31 '20 at 12:16
@Holger true I'm new to regex and I completely forgot that I dont need the "or" operation here – Attila jáger Jul 31 '20 at 12:21
3

But if `P` is supposed to match the end of `TIMESTAMP`, mind that you’re back kludging the pattern when it comes to `DATE`, `BOOLEAN`, or something ending with `BLOB` or `TEXT`, etc. And, as said, SQL is not case sensitive. Perhaps, you just want “a letter or `)`”, but you can easily run into false positives then. – Holger Jul 31 '20 at 12:24

score 1 · Answer 2 · answered Jul 31 '20 at 12:42

Using your example SQL string, this is the result from one SQL Formatter web site.

CREATE TABLE book 
  ( 
     page      NUMBER(20, 0), 
     author    VARCHAR2(255), 
     nooflines NUMBER(100) 
  )

As you can see, just for this one SQL statement, there was a bit of work done.

Upper case all SQL keywords.
Left justify the column names and the column definitions.

Meaning that you have to identify SQL keywords, column names, and column definitions. Let's not forget the major SQL statements; SELECT, INSERT, UPDATE, DELETE, and the combinations, like INSERT UPDATE.

If this is what you want to do, you're going to have to write a good bit of code. One regex isn't going to get you there.

What will be the java code to make an SQL stored as String in multi line?

2 Answers2