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 <[EMAIL PROTECTED]> 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
