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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
