Below are some MySQL functions that might help with your problem.
Sorry, for not much of direct answer.
But the idea is that you can combine some of these (string) functions ->
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html to parse
out the string you are looking for. If you are programming in one of
Unix, you could also pull your answer into UNIX script to parse it out
what ever you need.
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special form of
CONCAT(). The first argument is the separator for the rest of the
arguments. The separator is added between the strings to be
concatenated. The separator can be a string as can the rest of the
arguments. If the separator is NULL, the result is NULL. The function
skips any NULL values after the separator argument.
mysql> SELECT CONCAT_WS(',',
'First name','Second name','Last Name');
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
-> 'First name,Last Name'
Before MySQL 4.0.14, CONCAT_WS() skips empty strings as well as NULL
Returns a value 1 to N if the string str is in the string list strlist
consisting of N substrings. A string list is a string composed of
substrings separated by `,' characters. If the first argument is a
constant string and the second is a column of type SET, the
FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if
str is not in strlist or if strlist is the empty string. Returns NULL if
either argument is NULL. This function will not work properly if the
first argument contains a comma (`,') character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
Returns the position of the first occurrence of substring substr in
string str. This is the same as the two-argument form of LOCATE(),
except that the arguments are swapped.
mysql> SELECT INSTR('foobarbar', 'bar');
mysql> SELECT INSTR('xbar', 'foobar');
This function is multi-byte safe. In MySQL 3.23, this function is case
sensitive. For 4.0 on, it is case sensitive only if either argument is a
Returns the leftmost len characters from the string str.
mysql> SELECT LEFT('foobarbar', 5);
Returns the length of the string str, measured in bytes. A multi-byte
character counts as multiple bytes. This means that for a string
containing five two-byte characters, LENGTH() returns 10, whereas
CHAR_LENGTH() returns 5.
mysql> SELECT LENGTH('text');
From: Peter Brawley [mailto:peter.brawley@stripped]
Sent: Thursday, November 17, 2005 1:51 PM
To: Rick Dwyer
Subject: Re: A bit of SQL help for a MySQL novice.
>I need to read the first 4 positions in the phone number to determine
>My statement looks like this:
>'Select mid(phone, 1,4) as phoneareacode from phonetable'
>This works but if the number is entered as 1(203)-555-1212 the above
would return "1(20" which is not >what I am looking for.
You need an unpunct() function. Not available in 4 or 5, easy to write
in 5.0 as a stored function, not hard to add as a 'C' udf in 4.1 if you
write 'C'. Since it's a common requirement, likely someone has written
it. Failing that, you may be stuck with the absurd
Rick Dwyer wrote:
> Hello All.
> I am hoping for a bit of help with some code that has really given me
> some trouble. If this is not he correct forum for this any help in
> pointing me to a more suited list would be appreciated.
> I have a MySQL 4.1.x database containing records with phone numbers.
> Most of the phone numbers are enter in 12035551212 format, but some
> are entered with spaces or "-" or "(" or other characters.
> I need to read the first 4 positions in the phone number to determine
> it's location.
> My statement looks like this:
> 'Select mid(phone, 1,4) as phoneareacode from phonetable'
> This works but if the number is entered as 1(203)-555-1212 the above
> would return "1(20" which is not what I am looking for.
> Is there a way to have the select statement examine only numeric
> values in the phone number so it would disregard the other charcters?
> In Lasso, you can use a Replace with a Regular Expression function to
> have just the digits 0-9 examined but haven't been able find a way to
> do this in SQL.
> Any help is appreciated.
> Thank you.
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date:
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql