From: Joerg Bruehe (joergmysql.com)
Date: Fri Jul 27 2007 - 03:05:37 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi Brent, Lars, all !

While I cannot offer a solution, I still know the "replace()" approach
is incorrect:

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
addresses):

- There are streets that have digits in their name, just two examples
   from Berlin:
      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:
      Straße 217

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
> there.

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
>> field
>> only?
>> like when having german street addresses (e.g. foostreet 23) in a varchar
>> field
>> 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.

Sorry,
Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]