Re: TRIM to replace?

2004-01-26 Thread Roger Baklund 
* Roger Baklund
> UPDATE table SET field=CONCAT(0,TRIM(LEADING '49' FROM field));

.. and this will of course prefix _all_ numbers with a '0', not only those
that started with '49'... you should append WHERE field LIKE '49%' to you
update statement.

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: TRIM to replace?

2004-01-26 Thread Roger Baklund 
* Bernd Tannenbaum 
> Me needs to change the format of some telephone numbers which are 
> placed in a mysql database.
> 
> Current format:   49xyz
> Wanted format:0xyz
> 
> Now i know how to remove the leading 49:
> UPDATE table SET field=(TRIM(LEADING '49' FROM field));
> 
> But how can i replace the "49" with a "0"?
> Or maybe in 2 steps, first remove 49, then add 0?

You can do it one step, using the CONCAT function:

UPDATE table SET field=CONCAT(0,TRIM(LEADING '49' FROM field));

-- 
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]