473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Little Quiz with regular expression ;)

I was traing to figure out an expression finding table names in sql
query. Table name is known, for example 'table'.

Below is sample code. This code should find last four strings ONLY.
Other strings containing 'table' should be omitted.

<?php
$pattern = YOUR PROPOSAL

$string = 'SELECT tableDupa,table1,otable FROM table,table ,
table , table';

preg_match_all($pattern, $string, $a);

?>

Thanks for help ;)
Jun 2 '08 #1
12 1656
lk*******@gmail.com schreef:
I was traing to figure out an expression finding table names in sql
??? traing ???
query. Table name is known, for example 'table'.

Below is sample code. This code should find last four strings ONLY.
Other strings containing 'table' should be omitted.

<?php
$pattern = YOUR PROPOSAL

$string = 'SELECT tableDupa,table1,otable FROM table,table ,
table , table';

preg_match_all($pattern, $string, $a);

?>

Thanks for help ;)
$pattern = '/table?[ ,\']/' ;

--
Luuk
Jun 2 '08 #2
On Sun, 18 May 2008 16:45:11 +0200, Luuk <Lu**@invalid.lanwrote:
lk*******@gmail.com schreef:
>I was traing to figure out an expression finding table names in sql

??? traing ???
>query. Table name is known, for example 'table'.
Below is sample code. This code should find last four strings ONLY.
Other strings containing 'table' should be omitted.
<?php
$pattern = YOUR PROPOSAL
$string = 'SELECT tableDupa,table1,otable FROM table,table ,
table , table';
preg_match_all($pattern, $string, $a);
?>
Thanks for help ;)

$pattern = '/table?[ ,\']/' ;
Allthough you can find the single word, and in your particular string
would be easy to do, there is no way your goal (finding out the table
name) is obtainable through pure regexes without a regard for SQL syntax..
There are lesser hurdles like avoiding matching the string against a
literal in a string, which would mean you'll have to take quote characters
in mind, with bigger ones like fieldnames the same as the table, literal
aliasses, and more, all not reflected in your example.

Believe you me, I enjoy regexes, but they have their place. If you need
this functionality, you need an SQL query parser, not regexes.
--
Rik Wasmus
....spamrun finished
Jun 2 '08 #3
On Sun, 18 May 2008 17:23:43 +0200, Rik Wasmus
<lu************@hotmail.comwrote:
On Sun, 18 May 2008 16:45:11 +0200, Luuk <Lu**@invalid.lanwrote:
>lk*******@gmail.com schreef:
>>I was traing to figure out an expression finding table names in sql

??? traing ???
>>query. Table name is known, for example 'table'.
Below is sample code. This code should find last four strings ONLY.
Other strings containing 'table' should be omitted.
<?php
$pattern = YOUR PROPOSAL
$string = 'SELECT tableDupa,table1,otable FROM table,table ,
table , table';
preg_match_all($pattern, $string, $a);
?>
Thanks for help ;)

$pattern = '/table?[ ,\']/' ;

Allthough you can find the single word, and in your particular string
would be easy to do, there is no way your goal (finding out the table
name) is obtainable through pure regexes without a regard for SQL
syntax. There are lesser hurdles like avoiding matching the string
against a literal in a string, which would mean you'll have to take
quote characters in mind, with bigger ones like fieldnames the same as
the table, literal aliasses, and more, all not reflected in your example.

Believe you me, I enjoy regexes, but they have their place. If you need
this functionality, you need an SQL query parser, not regexes.
I'm sorry, Luuk, this here above was meant to be a direct reply to the OP.
About your post, yours would amongst others fail on:
- "SELECT foo FROM anothertable"
- "SELECT foo FROM tabl"
- "SELECT table FROM tabledescriptions"
--
Rik Wasmus
....spamrun finished
Jun 2 '08 #4
I know that this metod will not work in every case but for my purpose
it will be enough. The database structure is known. It contains few
small tables (around 20 records in each). Table names are in english
but contents of database are in polish.

Jun 2 '08 #5
On Sun, 18 May 2008 17:54:23 +0200, <lk*******@gmail.comwrote:
I know that this metod will not work in every case but for my purpose
it will be enough. The database structure is known. It contains few
small tables (around 20 records in each). Table names are in english
but contents of database are in polish.
Wel then, at least try to use MYSQL syntax where tablenames can appear to
your advantage, I'd say just try to grab tables, and match against them
after that, or this quick example (untested):

/(FROM|INTO|UPDATE|DELETE|JOIN)\s+((`[^`]+`|[a-z0-9_$]+)\s*(AS.*?)?\s*,\s*)*?(`)?(table)[`\s]/si

...allthough I'm no enterily sure what happens with for example "SELECT *
FROM table as x, table as y WHERE ...", don't feel like testing un a lazy
sunday :P.
--
Rik Wasmus
....spamrun finished
Jun 2 '08 #6
Rik Wasmus schreef:
On Sun, 18 May 2008 17:23:43 +0200, Rik Wasmus
<lu************@hotmail.comwrote:
>On Sun, 18 May 2008 16:45:11 +0200, Luuk <Lu**@invalid.lanwrote:
>>lk*******@gmail.com schreef:
I was traing to figure out an expression finding table names in sql

??? traing ???

query. Table name is known, for example 'table'.
Below is sample code. This code should find last four strings ONLY.
Other strings containing 'table' should be omitted.
<?php
$pattern = YOUR PROPOSAL
$string = 'SELECT tableDupa,table1,otable FROM table,table ,
table , table';
preg_match_all($pattern, $string, $a);
?>
Thanks for help ;)

$pattern = '/table?[ ,\']/' ;

Allthough you can find the single word, and in your particular string
would be easy to do, there is no way your goal (finding out the table
name) is obtainable through pure regexes without a regard for SQL
syntax. There are lesser hurdles like avoiding matching the string
against a literal in a string, which would mean you'll have to take
quote characters in mind, with bigger ones like fieldnames the same as
the table, literal aliasses, and more, all not reflected in your example.

Believe you me, I enjoy regexes, but they have their place. If you
need this functionality, you need an SQL query parser, not regexes.

I'm sorry, Luuk, this here above was meant to be a direct reply to the
OP. About your post, yours would amongst others fail on:
- "SELECT foo FROM anothertable"
- "SELECT foo FROM tabl"
- "SELECT table FROM tabledescriptions"
appologize accepted,.. ;-)

but my post was just a try to get something more out of the OP, and in
his post was the text "Other strings containing 'table' should be
omitted", so i did litterally what he asked..

if i wanted this to do for myself, i think i would include the 'FROM'
(in case of a SELECT) , to make sure the text i'm looking i the right place.

--
Luuk
Jun 2 '08 #7
Rik your regex

/(FROM|INTO|UPDATE|DELETE|JOIN)\s+((`[^`]+`|[a-z0-9_$]+)\s*(AS.*?)?\s*,
\s*)*?(`)?(table)[`\s]/si

is pretty cool, but doesn't work ;)
I have tried some simpler patters like:

/\W+table\W+/
/[,]|\s+table[,]|\s+/

and others similar, but I didn't succeed. I am not good in regular
expressions. I have no idea how to properly find all ocurences of
'table'. Don't bother with SELECT|UPDATE|FROM etc I remember to
include this. I just need to know how to properly filter string
'table' from alphanumeric combinations containing table like atable1.
Please don't forget that strings like:

FROM another_table table, ...

should also be omitted(alias musn't be treated as table name).

SELECT tableDupa,table1,otable FROM table,table , table , table

Thanks for any help.
Jun 2 '08 #8
On Sun, 18 May 2008 20:56:47 +0200, <lk*******@gmail.comwrote:
Rik your regex

/(FROM|INTO|UPDATE|DELETE|JOIN)\s+((`[^`]+`|[a-z0-9_$]+)\s*(AS.*?)?\s*,
\s*)*?(`)?(table)[`\s]/si

is pretty cool, but doesn't work ;)
I have tried some simpler patters like:

/\W+table\W+/
/[,]|\s+table[,]|\s+/

and others similar, but I didn't succeed. I am not good in regular
expressions. I have no idea how to properly find all ocurences of
'table'. Don't bother with SELECT|UPDATE|FROM etc I remember to
include this. I just need to know how to properly filter string
'table' from alphanumeric combinations containing table like atable1.
Please don't forget that strings like:

FROM another_table table, ...

should also be omitted(alias musn't be treated as table name).

SELECT tableDupa,table1,otable FROM table,table , table , table
As said before, you need an SQL parser. With some adjustments, the regex
kicked of like above could work, but it's an inane exercise. Why don't you
tell us WHY you think you need this? If it's about highlighting, use
packages allready out there, if it's about rights, arrange those at the
proper level, i.e. the database, if it's about you needing to change a
tablename that doesn't exist anymore in queries, temporarily define a view
with that name to keep things going, and just change the queries
themselves in your code.
--
Rik Wasmus
....spamrun finished
Jun 2 '08 #9
Rik you have right I should explain why I need this.

I am writing a SQL learning platform where every student can practice
sql on a sample database with 6 tables and with about 20 records in
each. Every student works on his own copy of database. It looks simple
but problem is that I have only one database where I have to store
databases for every student in the system. I achieve this by adding
user id to every table in the database. Students should work wihout
bothering about this so I have to add prefixes to tables in all
submited queries.

As you said I can write SQL parser but this is complicated and not
necessary in this particular case. Since the database structure is
known I can get table names via

SHOW TABLES LIKE 'user_id%';

Then for each table I can perform

DESCRIBE table;

to retrieve column names. With this knowledge I can prepere queries to
execute.

I've developed this algoritm:

1. Find out whitch tables are used in query. (Subject of our topic)
2. For each column in query add adequate table name with prefix.

I know that this is not ideal algoritm but it will suit my needs ;)
Jun 2 '08 #10
On Sun, 18 May 2008 12:45:19 -0700 (PDT), in comp.lang.php
lk*******@gmail.com
<81**********************************@c58g2000hsc. googlegroups.com>
wrote:
>| Rik you have right I should explain why I need this.
|
| I am writing a SQL learning platform where every student can practice
| sql on a sample database with 6 tables and with about 20 records in
| each. Every student works on his own copy of database. It looks simple
| but problem is that I have only one database where I have to store
| databases for every student in the system. I achieve this by adding
| user id to every table in the database. Students should work wihout
| bothering about this so I have to add prefixes to tables in all
| submited queries.
|
| As you said I can write SQL parser but this is complicated and not
| necessary in this particular case. Since the database structure is
| known I can get table names via
|
| SHOW TABLES LIKE 'user_id%';
|
| Then for each table I can perform
|
| DESCRIBE table;
|
| to retrieve column names. With this knowledge I can prepere queries to
| execute.
|
| I've developed this algoritm:
|
| 1. Find out whitch tables are used in query. (Subject of our topic)
| 2. For each column in query add adequate table name with prefix.
|
| I know that this is not ideal algoritm but it will suit my needs ;)
There a 2 solutions to this problem.
Either create individual databases for each student then issue the
command USE <dbNameprior to accessing the database. This way the
students learn another command.
Or get WOS from CHSoftware.net. This allows the student to install
Apache, php and mysql on their usb drives. There shouldn't be any
configuration problems if there is no other web server active.
Jun 2 '08 #11
On 18 May, 20:45, lkrzys...@gmail.com wrote:
Rik you have right I should explain why I need this.

I am writing a SQL learning platform where every student can practice
sql on a sample database with 6 tables and with about 20 records in
each.
Well don't teach them to use implicit comma joins, use explicit JOIN
syntax instead!
Jun 2 '08 #12
Rik Wasmus wrote:
On Sun, 18 May 2008 17:54:23 +0200, <lk*******@gmail.comwrote:
>I know that this metod will not work in every case but for my purpose
it will be enough. The database structure is known. It contains few
small tables (around 20 records in each). Table names are in english
but contents of database are in polish.

Wel then, at least try to use MYSQL syntax where tablenames can appear
to your advantage, I'd say just try to grab tables, and match against
them after that, or this quick example (untested):

/(FROM|INTO|UPDATE|DELETE|JOIN)\s+((`[^`]+`|[a-z0-9_$]+)\s*(AS.*?)?\s*,\s*)*?(`)?(table)[`\s]/si
..allthough I'm no enterily sure what happens with for example "SELECT *
FROM table as x, table as y WHERE ...", don't feel like testing un a
lazy sunday :P.
--Rik Wasmus
...spamrun finished
The /x modifier is your friend. Also, use \w for words, since it's
locale-specific, not to mention shorter. Here are a few improvements:

/
(?:from|into|update|delete|join) # no need for capturing
\s+?
(?:
`?\w+?`?
\s*?
(?:as.*?)?
\s*?,\s*?
)*?
`?(table)[`\s]
/sxi

I think this works. lol
Jun 2 '08 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: DJ | last post by:
Here are my answers for quiz 2 - how well did I do? 1. Which of the following types cannot be used to subscript an array? a. short b. char c. float d. all of the above types may be used to...
4
by: Buddy | last post by:
Can someone please show me how to create a regular expression to do the following My text is set to MyColumn{1, 100} Test I want a regular expression that sets the text to the following...
4
by: Neri | last post by:
Some document processing program I write has to deal with documents that have headers and footers that are unnecessary for the main processing part. Therefore, I'm using a regular expression to go...
14
by: tranky | last post by:
Hi, i'm italian...so...excuse me for my english. I've a little problem....in what manner i can check a textbox for know if it contain only character from A-Z (a-z), numbers (0-9), and underscore...
7
by: Billa | last post by:
Hi, I am replaceing a big string using different regular expressions (see some example at the end of the message). The problem is whenever I apply a "replace" it makes a new copy of string and I...
13
by: Kiuhnm | last post by:
How would you implement a static dictionary with macros? For example, the five lines PL_FIND(MYDICT, HOUSE) PL_FIND(MYDICT, AUTO) PL_FIND(MYDICT, THIS_IS_NOT_IN_THE_DICTIONARY)...
25
by: Mike | last post by:
I have a regular expression (^(.+)(?=\s*).*\1 ) that results in matches. I would like to get what the actual regular expression is. In other words, when I apply ^(.+)(?=\s*).*\1 to " HEART...
1
by: Allan Ebdrup | last post by:
I have a dynamic list of regular expressions, the expressions don't change very often but they can change. And I have a single string that I want to match the regular expressions against and find...
1
by: Holly Six | last post by:
I have a quiz file using the e-learning and quiz components. There is a bug for one condition - the user takes the quiz and fails, click the retake button and passes. The score that is written to the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.