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
– Ravinder Reddy, Commented Apr 23, 2014 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
– Mike Brant, Commented Apr 23, 2014 at 17:47
you'll have to skim this task in scripting language, MySQL or RDBM is not advisable for this job!! :) — NoobEditor
– NoobEditor, Commented Apr 23, 2014 at 17:48

Ravinder Reddy · Accepted Answer · 2014-04-23 18:10:12Z

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, 2014 at 18:10

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

NoobEditor Over a year ago

question : assuming the txt column contains 100 rows....how do u plan to loop over it for pattern replacing?

Atwp67 Over a year ago

@Ravinder I'll definitely give this a go!

Atwp67 Over a year ago

@Ravinder The code worked perfectly! Only hiccup was some text whereby http was entered twice. Thanks again.

Ravinder Reddy Over a year ago

@Atwp67: Yes. And I mentioned the same, in the first line of my answer.

Atwp67 Over a year ago

@Ravinder I know, but you got me 99.9% of the way there. Again, I appreciate your help.

Collectives™ on Stack Overflow

Remove web address from text string

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related