List:General Discussion« Previous MessageNext Message »
From:R Talbot Date:August 12 2001 3:19pm
Subject:Substring from varchar Column
View as plain text  
I can' seem to find an answer in the manual or outside references.
From the manual this is an example
mysql> select comment from tbl_name,substring(comment,20) as substr
ORDER BY substr;
   Now my understanding is I can, using the above type query, extract a
substring from a particular field.
 From the tutorial in the MySQL manual  the database menagerie the table
pet..
+----------+----------+---------+------+------------+------------+
| name     | owner    | species | sex  | birth      | death      |
+----------+----------+---------+------+------------+------------+
| Fluffy      | Harold   | cat       | f      | 1993-02-04 | NULL
|
| Claws     | Gwen     | cat      | m    | 1994-03-17 | NULL       |
| Buffy       | Harold   | dog      |  f     | 1989-05-13 | NULL       |

| Fang      | Benny    | dog      | m    | 1990-08-27 | NULL       |
| Bowser   | Diane    | dog      | m    | 1998-08-31 | 1995-07-29 |
| Chirpy    | Dr. Gwen | bird    | f      | 1998-09-11 | NULL       |
| Whistler | Gwen     | bird     | f      |   NULL           |
NULL       |
| Slim        | Benny    | snake | m    | 1996-04-29  | NULL       |
+----------+----------+---------+------+------------+------------+
8 rows in set (0.03 sec)

To extract the substring "Gwen" from the field Owner  Row="Chirpy" .....

SELECT name owner from pet,substring(owner, 3) where name="Chirpy"
doesn't work

but this works..??
SELECT substring("Dr. Gwen", (locate(".","Dr. Gwen", 1) + 2))
 returns Gwen

Thread
Substring from varchar ColumnR Talbot12 Aug
  • RE: Substring from varchar ColumnDon Read12 Aug