Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 9th, 2012  |  syntax: None  |  size: 0.57 KB  |  hits: 25  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. How can I combine the LOCATE() and SUBSTR() functions in MySQL to create a new column?
  2. id | url
  3. 1  | http://www.foo.com/ZedFe.htm
  4. 2  | www.foo.com/tEzqz.htm?q=eee
  5. 3  | foo.com/ZeeFg.htm
  6. 4  | http://www.foo.com/lkeiO
  7. etc.
  8.        
  9. $id = substr($url, strpos($url,'foo.com/') + 8, 5);
  10.        
  11. SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(LEADING
  12. "https://" FROM TRIM(LEADING "http://" FROM TRIM(url))), "/", 1), ":",
  13. 1), ".", IF(url LIKE "%.org.__%" OR url LIKE "%.net.__%" OR url LIKE
  14. "%.com.__%" OR url LIKE "%.__.us%" OR url LIKE "%.co.__%" OR url LIKE
  15. "%.__.uk%", -3, -2) )
create a new version of this paste RAW Paste Data