List:General Discussion« Previous MessageNext Message »
From:Mikhail Berman Date:November 17 2005 7:46pm
Subject:RE: A bit of SQL help for a MySQL novice.
View as plain text  
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:peter.brawley@stripped] 
Sent: Thursday, November 17, 2005 1:51 PM
To: Rick Dwyer
Cc: mysql@stripped
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/mysql?unsub=1

Thread
A bit of SQL help for a MySQL novice.Rick Dwyer17 Nov
  • Re: A bit of SQL help for a MySQL novice.SGreen17 Nov
  • Re: A bit of SQL help for a MySQL novice.Rhino17 Nov
    • Re: A bit of SQL help for a MySQL novice.Rick Dwyer17 Nov
  • Re: A bit of SQL help for a MySQL novice.Rhino17 Nov
  • Re: A bit of SQL help for a MySQL novice.Peter Brawley17 Nov
RE: A bit of SQL help for a MySQL novice.Mikhail Berman17 Nov