Re: regex

Hi Peter. Is this what you want to do. Copy this into a *.php page, and then look at it with your browser. I just refactored one of my heredoc queries to handle your problem. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <title>SQL Regex Tester</title> </head> <body> <?php echo "Version 1 <br />"; $this_table = 'MSS TABLE NAME'; $this_id = 5; $sql = <<<OUT select * from $this_table where ID = $this_id OUT; echo "Contents of \$sql: <br />$sql"; echo "<br /><br /> Version 2 <br />"; $sql = "SELECT DISTINCT [Table Name].[Column.Name], [Table Name1].[Column Name 2]"; echo "Contents of \$sql: <br />$sql"; echo "<br /><br /> Version 3 <br />"; $this_table = 'MSS TABLE NAME'; $this_id = 5; $sql = <<<OUT SELECT DISTINCT [Table Name].[Column.Name], [Table Name1].[Column Name 2] OUT; echo "Contents of \$sql: <br />$sql"; echo "<br /><br /> Version 4 <br />"; $tbl_name = "[Table Name]"; $col_name = "[Column Name]"; $tbl_name1 = "[Table Name1]"; $col_name2 = "[Column Name 2]"; $sql = <<<OUT SELECT DISTINCT $tbl_name.$col_name, $tbl_name1.$col_name2 OUT; echo "Contents of \$sql: <br />$sql"; echo "<br /><br /> Version 5 <br />"; $sql_query = "SELECT DISTINCT"; $tbl_name = "[Table Name]"; $col_name = "[Column Name]"; $tbl_name1 = "[Table Name1]"; $col_name2 = "[Column Name 2]"; $tbl_name = strtolower($tbl_name); $col_name = strtolower($col_name); $tbl_name1 = strtolower($tbl_name1); $col_name2 = strtolower($col_name2); $sql = <<<OUT $sql_query $tbl_name.$col_name, $tbl_name1.$col_name2 OUT; echo "Contents of \$sql: <br />$sql"; echo "<br /><br /> Version 6 <br />"; $sql_query = "SELECT DISTINCT"; $tbl_name = "[Table Name]"; $col_name = "[Column Name]"; $tbl_name1 = "[Table Name1]"; $col_name2 = "[Column Name 2]"; // convert to lower case $tbl_name = strtolower($tbl_name); $col_name = strtolower($col_name); $tbl_name1 = strtolower($tbl_name1); $col_name2 = strtolower($col_name2); // remove '[]' characters $tbl_name = trim($tbl_name, "[]"); $col_name = trim($col_name, "[]"); $tbl_name1 = trim($tbl_name1, "[]"); $col_name2 = trim($col_name2, "[]"); // replace space with '_' character $tbl_name = preg_replace('/\s/', '_', $tbl_name); $col_name = preg_replace('/\s/', '_', $col_name); $tbl_name1 = preg_replace('/\s/', '_', $tbl_name1); $col_name2 = preg_replace('/\s/', '_', $col_name2); $sql = <<<OUT $sql_query $tbl_name.$col_name, $tbl_name1.$col_name2 OUT; echo "Contents of \$sql: <br />$sql"; ?> </body> </html> The above code is not optimal, but it works OK. I use the heredoc construct to build my sql query strings. Saves using all those single and double quote characters. http://uk2.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc HTH Keith Roberts ----------------------------------------------------------------- Websites: http://www.karsites.net http://www.php-debuggers.net http://www.raised-from-the-dead.org.uk All email addresses are challenge-response protected with TMDA [http://tmda.net] ----------------------------------------------------------------- On Mon, 21 Jan 2008, Peter wrote:

To: php-general@lists.php.net From: Peter <tasmaniac@iprimus.com.au> Subject: Re: [PHP] regex Well actually not a real lot so far. I'm just trial and error(lots of that) at the moment. I've only been 'playing with php for about a month or so. $file = "phptest1.txt"; $rep = array ("tbl_" , "_%", "bool default 0", "bool default 1", '?'); $wih = array ("", "_pc", "bool DEFAULT FALSE", "bool DEFAULT TRUE", "" ); if (is_file($file)) : $fh = fopen($file, "r+") or die("File does not exist"); while (! feof($fh)) : $line = fgets($fh,4096); $str = strtolower($line); $str_fixed = str_replace($rep, $wih, $str);

     print $str_fixed . "<br />";

endwhile; Then as far as the regexp part for replacing the space inbetween [blah blah blah] with _ goes it was a case of try delete try delete etc. I may need to break the string into an array but that in its self adds its own problems. Think one of my problems is Im try to run before I can crawl with php, postgre regex etc. Also its fun trying to workout things when all the books you come across are php/mysql. Came across an old message in my trawl of the news group that 'may' help going to give that a try as soon as I get the time. (it was more to do with replacing | with space between " " but it maybe convertable) "Keith Roberts" <keith@karsites.net> wrote in message news:alpine.LFD.1.00.0801211031370.31657@www.karsites.net...

Can yo upost the code you have got to do the conversion so far please? Regards Keith ----------------------------------------------------------------- Websites: http://www.karsites.net http://www.php-debuggers.net http://www.raised-from-the-dead.org.uk All email addresses are challenge-response protected with TMDA [http://tmda.net] ----------------------------------------------------------------- On Mon, 21 Jan 2008, Peter wrote:

To: php-general@lists.php.net From: Peter <tasmaniac@iprimus.com.au> Subject: [PHP] regex I am trying to convert ms access sql to postgresql using php. I have a sql statement in the form ;- $sql = SELECT DISTINCT [Table Name].[Column.Name], [Table Name 1].[Column Name 2] etc. what I want to end up with is $sql = SELECT DISTINCT table_name.column_name, table_name_1.column_name_2, ........ I have managed to get the caps to lower but I cant work out how to put the _ in place of spaces if the spaces are between [ ]. I either end up with S_E_L_E_C ..... or SELECT_DISTINCT_ etc... . Naturally I have only used part of sql statement and table, column names have been changed. (Think the one I'm trying on is 2000+ characters. So its not a case of set number of words/numbers between [] it could be 2 or it could be 4 etc) Anyone workout what I am talking about and can help would be appreciated. Peter