How to replace part of a string in MySQL?

I usually rename the returned value as a new field, in this case phone_replaced:

SELECT REPLACE(`phone`, 'Phone: ', '') as `phone_replaced` FROM `events`

But you can also keep the current name phone:

SELECT REPLACE(`phone`, 'Phone: ', '') as `phone` FROM `events`

As long as you alias it using as.

This actually works:

create table T (data char(40));
insert into T values
 ('Phone: 1235678'),
 ('Tel: 0505074455'),
 ('Phone: 0418192244');

select REPLACE(data, 'Phone: ','') from T;

Result:

REPLACE(DATA, 'PHONE: ','')
1235678
Tel: 05-05-07-44-55
0418192244

If you only want "matching" lines, you should add a relevant where clause:

select REPLACE(data, 'Phone: ','') from T
  where data LIKE 'Phone: %';

If you really want numbers you might use CAST ... as DECIMAL

select CAST(REPLACE(data, 'Phone: ','') as DECIMAL) from T
  where data LIKE 'Phone: %';

If you have to deal with case, try normalizing the string using LOWER:

select CAST(REPLACE(LOWER(data), 'phone: ','') as DECIMAL) from T
  where LOWER(data) LIKE 'phone: %';

That later would not perform well because MySQL won't be able to use the potential index on data I think. If you need, you should use case insensitive collation for the datacolumn. But that's an other story...

http://sqlfiddle.com/#!2/38218d/7

You can achieve it by using the PHP. Use it:

$phone = "Phone: 1234567"
$arr = explode(" ",$phone);
$phone = $arr[1];

Or use the SQL syntax:

SELECT REPLACE("Phone: 1234567",  'Phone: ',  '' ) FROM EVENTS

You try :-

SELECT LTRIM(phone) FROM EVENTS;

Read more about LTRIM.

For Replaceing :-

SELECT REPLACE(phone,phone,LTRIM(phone)) FROM EVENTS;

Hope it will help you.

make sure that names are case sensitive here so Phone: is not same as phone:

     SELECT case when phone like 'phone%' then REPLACE( phone,  'phone: ',  '' ) 
                 when phone like 'Phone%' then REPLACE( phone,  'Phone: ',  '' )
            end as Phone_number
     FROM EVENTS