Hi Rick, 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. I. 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 values. II. FIND_IN_SET(str,strlist) FIND_IN_SET(str,strlist) 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'); -> 2 III. INSTR(str,substr) 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'); -> 4 mysql> SELECT INSTR('xbar', 'foobar'); -> 0 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 binary string. IV. LEFT(str,len) LEFT(str,len) Returns the leftmost len characters from the string str. mysql> SELECT LEFT('foobarbar', 5); -> 'fooba' V LENGTH(str) LENGTH(str) 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'); -> 4 Mikhail Berman -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, November 17, 2005 1:51 PM To: Rick Dwyer Cc: mysql@lists.mysql.com Subject: Re: A bit of SQL help for a MySQL novice. Rick >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. 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 replace(replace(replace(replace(replace(@s,'(',''),')',''),' ',''),'-',''),'.',''). PB ----- 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. > Rick > > > > > > > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
