explode in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

explode in mysql

Post by thatsme »

I have field values in the form,

11-0010
11-2001

How to get the first characters before - character.
at present i am using,

SELECT * FROM table_name WHERE LEFT(field, 2) =$exploded_val[1];
this works fine. But it works only if the number of characters are 2 (that is 11 in the example ). Is there any regular expression function in mysql where i can get all the characters before the (-) character?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: explode in mysql

Post by Christopher »

Easier might be:

Code: Select all

list($first, $second) = explode('-', $value);
(#10850)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: explode in mysql

Post by Kieran Huggins »

indeed there is a MySQL regex!

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

exploding sounds a lot easier on the DB engine though, arborint++
mwasif
Forum Newbie
Posts: 13
Joined: Sun Jul 15, 2007 1:24 pm

Re: explode in mysql

Post by mwasif »

You can use MySQL's function SUBSTRING_INDEX().

Code: Select all

SELECT * FROM table_name WHERE SUBSTRING_INDEX(field,'-',1)=$exploded_val[1];
Kieran Huggins wrote:indeed there is a MySQL regex!
http://dev.mysql.com/doc/refman/5.0/en/regexp.html
It returns 1 if the pattern matches otherwise 0.
pekarna
Forum Newbie
Posts: 1
Joined: Sun May 10, 2009 1:17 am

Re: explode in mysql

Post by pekarna »

Post Reply