drop table with suffix

[mayub]

Hi Everyone,

I have already spent 4 hours on finding a solution to creating a dynamic query to drop tables that have a suffix. There are 4 tables in the database.
2 end with mayub. I need to write a dynamic sql to drop them. I am sure if will only work with SELECT, so the first query is wrong but the second did not work either.

TableName is a variable that holds table names

1
stmt = Con.createStatement();
String sufix = "mayub";
String DROP_TABLE = "if (" + TableName.endsWith(sufix) + ") drop table " + TableName;
stmt.execute(DROP_TABLE);

2
stmt = Con.createStatement();
String DROP_TABLE = "if exists(select table_name from information_schema.tables" +
"where table_name like '%mayub')"
+ " drop table " + TableName;
stmt.execute(DROP_TABLE);

Thanks,
Mayub

[mayub]

Could someone please help?

[OrangeDog]

Well this is really an SQL question rather than a Java one. If there are only 2, why not just execute a static query to drop them?

What are the final SQL statements you are sending and what are the error messages?

[mayub]

OrangeDog, thanks for asking!

Perhaps you are correct that it is an SQL rather than Java question, but I am using Java.

I am writing the program in java. This program needs to do everything dynamically. Saying that there are 2 tables was only an example. Basically I need to create tables with a suffix based on the tables that exist in the database. Thus for example, if x, y, z, are tables in the database, I need to create tables x_1, y_1, z_1. I have succeeded in doing that dynamically, meaning if the existing table is x, append _1 and so on. However, before I create them I must drop x_1, y_1, z_1, but not x,y,z. In the following

String dropTable = "drop table " + TableName. -- after the dot I see many methods in java. One such method is contains(CharSequence s), but it returns boolean, so the error is can't drop table TRUE. Substrings require beginIndex and would return string values, but do not give the correct result.
Using concat, i.e. "drop table " + TableName + "_1" makes the table X_1_1, Y_1_1, which does not exist. Any ideas?

[OrangeDog]

Make a list of the table names and remove the ones that don't match the "_1" pattern. Then just drop everything left in the list.