CODEWITHSUNDEEP
mysql
Atwp67
Atwp67

Reputation: 307

Remove web address from text string

This is one of those "can it be done" questions. I had a colleague approach me about removing web addresses - all unique except starting with http - from databased text strings. My first instinct was to go with the replace function but that can become quite cumbersome to use and maintain.

So I'm querying the forum on their thoughts on how to best approach this task.

 This is a test http://t.co/aBc689XYz -> new result=This is a test
 Have a nice http://t.co/vZ754PlkuI day -> new result=Have a nice day

Upvotes: 2

Views: 366

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

If the URL part exists only once in the text, the following should be working.

MySQL Solution: 

select concat( @pss:=substring_index( txt, 'http://', 1 ), 
               substring( @ss:=substring_index( txt, 'http://', -1 ), 
                          if( (@l:=locate( ' ', @ss )) > 0, @l+1, 0 ) ) 
       ) as txt
from (
  select 'This is a test http://t.co/aBc689XYz' as txt
  union all
  select 'Have a nice http://t.co/vZ754PlkuI day'
  union all
  select 'This worked http://sqlfiddle.com/#!2/d41d8 perfectly on sql fiddle'
) records
;

Results: 

+-------------------------------------+
| txt                                 |
+-------------------------------------+
| This is a test                      |
| Have a nice day                     |
| This worked perfectly on sql fiddle |
+-------------------------------------+

Demo @ MySQL 5.5.32 Fiddle

Upvotes: 3

Related Questions