RE: Howto search in SQL for a specific character?

["Albert" <albert@xxxxxxxxxxxx>]

Gustav Wiberg wrote:
> >> $v1 = chr(39); //39 is apostrofe
> >>
> >>
> >> $sql = "SELECT nameOfPedigree FROM tbpedigrees WHERE
> >> SUBSTR(nameOfPedigree,0,1) = $v1";
> >>
> >> Why doesn't this work?
> >>
> >> I want the sql to select all nameOfPedigree - fields where the first
> >> character is apostrofe (')

Here is what I have done using MySQL:

** Create a table:
	mysql> CREATE TABLE `tablename` (
	    -> `id` tinyint(3) unsigned NOT NULL auto_increment,
	    -> `name` char(20) default '0',
	    -> PRIMARY KEY  (`id`)
	    -> );
	Query OK, 0 rows affected (0.06 sec)

** Then insert some data into the table:
	mysql> INSERT INTO tablename (id, name) VALUES (NULL, '\'Albert');
	Query OK, 1 row affected (0.00 sec)

	mysql> INSERT INTO tablename (id, name) VALUES (NULL, '\'Piet');
	Query OK, 1 row affected (0.00 sec)

	mysql> INSERT INTO tablename (id, name) VALUES (NULL, '\'Koos');
	Query OK, 1 row affected (0.00 sec)

	mysql> INSERT INTO tablename (id, name) VALUES (NULL, 'Jan');
	Query OK, 1 row affected (0.00 sec)

	mysql> INSERT INTO tablename (id, name) VALUES (NULL, 'Gert');
	Query OK, 1 row affected (0.00 sec)

** Then retrieve all the record starting with an apostrophe ('):
	mysql> SELECT * FROM tablename WHERE name LIKE '\'%';
	+----+---------+
	| id | name    |
	+----+---------+
	|  1 | 'Albert |
	|  2 | 'Piet   |
	|  3 | 'Koos   |
	+----+---------+
	3 rows in set (0.00 sec)

Hope it helps

Albert

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.10/188 - Release Date: 2005/11/29
 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php