From: Lars Schwarz (lars.schwarzgmail.com)
Date: Fri Jul 27 2007 - 03:11:14 CDT

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

not to forget that there can also be street adresses like foobarstreet 23-25
or foobarstreet 17b and so on ... so i
guess i'll have to split my addresses into 2 fields from now on (street and
number). anyway thanks for ideas and
replies! best: lars

On 7/27/07, Joerg Bruehe <joergmysql.com> wrote:
>
> 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
>
>
>

--
Lars Schwarz
Gottorpstrasse 20
26122 Oldenburg
T 0441 2171 354 0
F 0441 2171 354 0
M 0179 512 4628

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