0

I have bunch of video data like this:

 <object width="560" height="315"><param name="movie" 
 value="http://www.youtube.com/v/2rwPGGdB1_4?version=3&hl=uk_UA&rel=0">
 </param><param name="allowFullScreen" value="true"></param><param 
 name="allowscriptaccess" value="always"></param><embed 
 src="http://www.youtube.com/v/2rwPGGdB1_4?version=3&hl=uk_UA&rel=0" 
 type="application/x-shockwave-flash" width="560" height="315" 
 allowscriptaccess="always" allowfullscreen="true"></embed></object>

I want to select and export ids from this set of information, as new site excepts ids only. So I should somehow grab the part between v/ and ?. How can I do this in SQL query?

Kannan K
  • 4,411
  • 1
  • 11
  • 25
Oletem
  • 19
  • 1
  • 11

2 Answers2

0

You can't really do that with pure MySQL. REGEXP only works as a filter and thus returns 0 or 1 as result, without providing access to the capturing group.

Solutions include either a custom function or using a different DBMS, as long as you want to do it strictly within SQL. How to do a regular expression replace in MySQL?

If it is possible for you to use any kind of scripting or compiled language, something like 

'<object width="560" height="315"><param name="movie"  value="http://www.youtube.com/v/2rwPGGdB1_4?version=3&hl=uk_UA&rel=0"> </param><param name="allowFullScreen" value="true"></param><param  name="allowscriptaccess" value="always"></param><embed  src="http://www.youtube.com/v/2rwPGGdB1_4?version=3&hl=uk_UA&rel=0"  type="application/x-shockwave-flash" width="560" height="315"  allowscriptaccess="always" allowfullscreen="true"></embed></object>'.match(/youtube\.com\/v\/([^?]+)?/);

should do the trick and, if PCRE compatible, would return the video id in the second capturing group.

derJake
  • 3
  • 2
0

I have found kind of solution in MySql. First, I have found the index of '/v/' with SELECT exvid_code, LOCATE('/v/', exvid_code), LOCATE('?', exvid_code) FROMexvid

After that, I have done substring to grab id:

SELECT SUBSTRING(exvid_code, 83,10) AS id FROM `exvid`
Oletem
  • 19
  • 1
  • 11