LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 09-10-2012, 10:43 AM   #1
masavini
Member
 
Registered: Jun 2008
Posts: 177

Rep: Reputation: 6
sql: put "beginning of line" and "end of line" within a charset...


hi,
i have this code:
Code:
$pattern = '/([A-Z])([0-9])/';
$replacement = '\\1 \\2';
$string = preg_replace($pattern, $replacement, $string);
	
$pattern = '/([0-9])([A-Z])/';
$replacement = '\\1 \\2';
$string = preg_replace($pattern, $replacement, $string);

$splitted = split('[ -. /\_]+',$string);
	
if (preg_match("/^[A-Z]+$/", $splitted[0])) {
	$qstring = $searchfield." REGEXP '[^A-Z]$splitted[0][^A-Z]'";
}
else {
	$qstring = $searchfield." REGEXP '[^0-9]$splitted[0][^0-9]'";
}

$i = 0;
while (++$i < count($splitted)) {
	if (!empty($splitted[$i])) {
		if (preg_match("/^[A-Z]+$/", $splitted[i])) {
			$qstring .= "AND ".$searchfield." REGEXP '[^A-Z]$splitted[i][^A-Z]'";
		}
		else {
			$qstring .= "AND ".$searchfield." REGEXP '[^0-9]$splitted[i][^0-9]'";
		}
	}
}
what happens is that if $splitted[i] is found at the beginning or at the end of my record, the query gives no results...

any idea?
i could add a space at the benigging and in the end all of my records... it would work, but it would be quite an horrible solution...

thanks!

Last edited by masavini; 09-10-2012 at 04:51 PM.
 
Old 09-11-2012, 08:09 AM   #2
Snark1994
Senior Member
 
Registered: Sep 2010
Location: Wales, UK
Distribution: Arch
Posts: 1,630
Blog Entries: 3

Rep: Reputation: 345Reputation: 345Reputation: 345Reputation: 345
What're the contents of 'string' and your database records likely to be? Just trying to get my head around what you're trying to do...

Also, surely '$splitted[i]' should be '$splitted[$i]'? (and would be easier to understand if you didn't put it in the SQL string directly) I would also change your while loop construct to a foreach loop, but that's more personal preference - though again it would make it easier to read.
 
Old 09-11-2012, 09:12 AM   #3
masavini
Member
 
Registered: Jun 2008
Posts: 177

Original Poster
Rep: Reputation: 6
Quote:
Originally Posted by Snark1994 View Post
What're the contents of 'string' and your database records likely to be? Just trying to get my head around what you're trying to do...
Code:
$string = "BUBU23GH"
$pattern = '/([A-Z])([0-9])/';
$replacement = '\\1 \\2';
$string = preg_replace($pattern, $replacement, $string);
	
$pattern = '/([0-9])([A-Z])/';
$replacement = '\\1 \\2';
$string = preg_replace($pattern, $replacement, $string);
now $string is "BUBU 23 GH"

Code:
$splitted = split('[ -. /\_]+',$string);
$splitted[0] is "BUBU"
$splitted[1] is "23"
$splitted[2] is "GH"

Code:
if (preg_match("/^[A-Z]+$/", $splitted[0])) {
	$qstring = $searchfield." REGEXP '[^A-Z]$splitted[0][^A-Z]'";
}
else {
	$qstring = $searchfield." REGEXP '[^0-9]$splitted[0][^0-9]'";
}
$qstring is "searchfield REGEXP '[^A-Z]BUBU[^A-Z]'"

Code:
$i = 0;
while (++$i < count($splitted)) {
	if (!empty($splitted[$i])) {
		if (preg_match("/^[A-Z]+$/", $splitted[i])) {
			$qstring .= "AND ".$searchfield." REGEXP '[^A-Z]$splitted[$i][^A-Z]'";  <- yes, i missed a "$"
		}
		else {
			$qstring .= "AND ".$searchfield." REGEXP '[^0-9]$splitted[$i][^0-9]'";  <- yes, i missed a "$"
		}
	}
}
now $qstring is "searchfield REGEXP '[^A-Z]BUBU[^A-Z]' AND searchfield REGEXP '[^0-9]23[^0-9]' AND searchfield REGEXP '[^A-Z]GH[^A-Z]'"

let's say i have a table with one field only (searchfield):
Code:
searchfield
BUBU23GH
_BUBU23GH_
_ABUBU23GH_
_BUBU233GH_
if i run the query i only get "_BUBU23GH_", since the record "BUBU23GH" does not match the expressions '[^A-Z]BUBU[^A-Z]' and '[^A-Z]GH[^A-Z]'
how to get both "_BUBU23GH_" and "BUBU23GH" with the results?

i have 2 ways:
1) modify the table records adding a particular char (like _) in the first and last position, then trim them away when getting the results (VERY uncomfortable)
2) modify the regular expressions so that [^A-Z] and [^0-9] include the special chars ^ and $ (begginning and end of line)... is it possible?

i hope it's a bit clearer, now...
 
Old 09-11-2012, 09:25 AM   #4
danielbmartin
Senior Member
 
Registered: Apr 2010
Location: Apex, NC, USA
Distribution: Ubuntu
Posts: 1,057

Rep: Reputation: 284Reputation: 284Reputation: 284
Quote:
Originally Posted by masavini View Post
i hope it's a bit clearer, now...
Not really.

It would help if you provide a sample input file and the desired output file.

Daniel B. Martin
 
Old 09-11-2012, 09:59 AM   #5
masavini
Member
 
Registered: Jun 2008
Posts: 177

Original Poster
Rep: Reputation: 6
ok, i try to...

sql to create a test table:
Code:
CREATE TABLE IF NOT EXISTS `test` (
  `searchfield` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` (`searchfield`) VALUES
('BUBU23GH'),
('_BUBU23GH_'),
('_ABUBU23GH_'),
('_BUBU233GH_');
php code to test the regular expressions:
Code:
$string = "BUBU23GH";

$pattern = '/([A-Z])([0-9])/';
$replacement = '\\1 \\2';
$string = preg_replace($pattern, $replacement, $string);

$pattern = '/([0-9])([A-Z])/';
$replacement = '\\1 \\2';
$string = preg_replace($pattern, $replacement, $string);

$splitted = split('[ -. /\_]+',$string);

if (preg_match("/^[A-Z]+$/", $splitted[0])) {
	$qstring = "searchfield REGEXP '[^A-Z]$splitted[0][^A-Z]'";
}
else {
	$qstring = "searchfield REGEXP '[^0-9]$splitted[0][^0-9]'";
}

$i = 0;
while (++$i < count($splitted)) {
	if (!empty($splitted[$i])) {
		if (preg_match("/^[A-Z]+$/", $splitted[$i])) {
			$qstring .= " AND searchfield REGEXP '[^A-Z]$splitted[$i][^A-Z]'";
		}
		else {
			$qstring .= " AND searchfield REGEXP '[^0-9]$splitted[$i][^0-9]'";
		}
	}
}

$query = "SELECT searchfield FROM test WHERE ".$qstring;
$mysqli = new mysqli($dbip, $user, $pwd, $dbname);
$result = $mysqli->query($query);
$out = $result->fetch_row();
foreach ($out as $k) {
      printf("$k");
}
current output is:
_BUBU23GH_

desired output is:
BUBU23GH
_BUBU23GH_

Last edited by masavini; 09-11-2012 at 10:02 AM.
 
Old 09-11-2012, 12:51 PM   #6
YankeePride13
Member
 
Registered: Aug 2012
Distribution: Ubuntu 10.04, CentOS 6.3, Windows 7
Posts: 185

Rep: Reputation: 43
Here's your query:

SELECT searchfield FROM test WHERE
searchfield REGEXP '[^A-Z]BUBU[^A-Z]'
AND
searchfield REGEXP '[^0-9]23[^0-9]'
AND
searchfield REGEXP '[^A-Z]GH[^A-Z]'

The reason you do not get BUBU23GH in the output is because of the first condition. There, you are saying to select all searchField where the string does not start and does not end with a letter, which obviously BUBU23GH does.
 
Old 09-11-2012, 03:11 PM   #7
masavini
Member
 
Registered: Jun 2008
Posts: 177

Original Poster
Rep: Reputation: 6
Quote:
Originally Posted by YankeePride13 View Post
The reason you do not get BUBU23GH in the output is because of the first condition. There, you are saying to select all searchField where the string does not start and does not end with a letter, which obviously BUBU23GH does.
sure, i know this!
is there a way to fix it?

i can't believe nobody ever had the need to specify a charset with beginning and end of lines... i mean something like [^A-Z$^]...
 
Old 09-19-2012, 08:03 AM   #8
masavini
Member
 
Registered: Jun 2008
Posts: 177

Original Poster
Rep: Reputation: 6
solved with:
Code:
	$unwanted = "[^0-9]";
	if (preg_match("/^[A-Z]+$/", $splitted[0])) {
		$unwanted = "[^A-Z]";
	}
	$qstring0 = "(".$searchfield1." REGEXP '$unwanted$splitted[0]$unwanted' OR ".$searchfield1." REGEXP '^$splitted[0]$unwanted' OR ".$searchfield1." REGEXP '$unwanted$splitted[0]$')";

	$i = 0;
	while (++$i < count($splitted)) {
		if (!empty($splitted[$i])) {
			$unwanted = "[^0-9]";
			if (preg_match("/^[A-Z]+$/", $splitted[$i])) {
				$unwanted = "[^A-Z]";
			}
			$qstring0 .= " AND (".$searchfield1." REGEXP '$unwanted$splitted[$i]$unwanted' OR ".$searchfield1." REGEXP '^$splitted[$i]$unwanted' OR ".$searchfield1." REGEXP '$unwanted$splitted[$i]$')";
		}
	}
thanks for your support!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
What i s this error pop-up: "On line 3: unknown type "evolution""?? kline Linux - Desktop 0 11-25-2011 04:12 PM
[SOLVED] How do I run "menu" and "logout" from the command line in fluxbox? psionl0 Slackware 19 03-02-2011 12:46 AM
Starting sshd: /etc/ssh/sshd_config line 60: garbage at end of line; "no". any clue? loba09 Linux - Server 1 02-17-2011 07:04 PM
printing hh in hh:mm using "awk '{FS=":";print $1}'" misses first line of output!! mayankmehta83 Linux - Newbie 2 12-03-2009 02:55 AM
Simple bash script "unexpected end of line error" snowman81 Programming 11 11-11-2007 09:31 AM


All times are GMT -5. The time now is 04:36 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration