Remove web address from text string

Question

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

Post some samples on existing data. And samples on expected results. — Ravinder Reddy, Apr 23 '14 at 17:41
Does this have to be done in MySQL only or is this better suited to be done at the application layer? — Mike Brant, Apr 23 '14 at 17:47
you'll have to skim this task in scripting language, MySQL or RDBM is not advisable for this job!! :) — NoobEditor, Apr 23 '14 at 17:48
@NoobEditor outside mysql, I was afraid that would be the response — Atwp67, Apr 23 '14 at 17:49
@Atwp67 Are relational database is not the best place to do this sort of thing. Is this a one time data transformation, or something you need to do all the time? — Mike Brant, Apr 23 '14 at 17:54
@Atwp67 Then my suggestion would be to actually insert a URL-free version of the data into the DB as you add new records rather than trying to do this on the fly at the time of record selection. — Mike Brant, Apr 23 '14 at 18:25

score 3 · Accepted Answer · answered Apr 23 '14 at 18:10

3

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

answered Apr 23 '14 at 18:10

Ravinder Reddy

23,692
6
52
82

**question :** assuming the `txt` column contains 100 rows....how do u plan to loop over it for pattern replacing? – NoobEditor Apr 23 '14 at 18:11
@Ravinder I'll definitely give this a go! – Atwp67 Apr 23 '14 at 18:19
@Ravinder The code worked perfectly! Only hiccup was some text whereby http was entered twice. Thanks again. – Atwp67 Apr 24 '14 at 13:01
@Atwp67: Yes. And I mentioned the same, in the first line of my answer. – Ravinder Reddy Apr 24 '14 at 14:10
@Ravinder I know, but you got me 99.9% of the way there. Again, I appreciate your help. – Atwp67 Apr 24 '14 at 14:25

Remove web address from text string

1 Answers1

Linked