Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Joerg Bruehe (joergmysql.com)
Date: Fri Jul 27 2007 - 03:05:37 CDT
Hi Brent, Lars, all !
While I cannot offer a solution, I still know the "replace()" approach
Brent Baisley wrote:
> The only regular expression MySQL support return a true/false if the
> expression was found. I had to do something similar to what you want to
> do. Although I needed to count how many digits there were.
> You can use the REPLACE() function to strip out the numbers. Of course,
> this means you need to do 10 replaces, 1 for each number.
> You can just nest them altogether like so:
> SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
> REPLACE(field, 1, ''), 2, ''), 3, '', 4,'', ... ) AS NoNums
> It's ugly, but it will get the job done.
No, it will do more than desired (if the data are arbitrary German
- There are streets that have digits in their name, just two examples
Straße des 17. Juni
Platz des 4. Juli
- There are streets that have no name but just a number, especially in
areas developed for building only very recently:
Applying your "replace()" command to such streets will modify the street
name in addition to removing the number of the house.
> If you are post processing the
> data on the front end, it may be quicker and certainly easier to do it
Agree - see below.
> On Jul 26, 2007, at 7:40 AM, Lars Schwarz wrote:
>> hi all, is there any shorthand to extract the string part of a varchar
>> like when having german street addresses (e.g. foostreet 23) in a varchar
>> and i want to select the non-numeric part of it (foostreet) only?
I fear there is no general solution to this, even if you had all kinds
of regular expressions available (say, pattern handling in Perl) -
unless you have very strict assumptions about the input addresses:
*If* you can rely on the format
(street name, maybe including digits and blanks) (blank) (digits)
then a suitable regular expression could strip the final string of
digits and the blank(s) directly preceding it, like this Perl line:
$address ~= s/^(.+) +\d+$/$1/ ; # untested
However, I am not aware of any SQL function supporting that.
Also beware that in case of a missing (house) number in a numbered
street this approach will ruin your data.
Most likely, you should do that in an application, with the additional
advantage that this would allow you to do a test run and report doubtful
cases before really modifying valid data.
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql