471,092 Members | 1,031 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

How to get all occurrence of a string in clob data in MySQL

I have one clob field 'Class_Data' in my table 'School' and in the clob there is one tag name as '<Fee-Value>' , I want to select the all occurrences of the tag from the clob data. I used the below query in mysql , but it select only the first occurrence of the tag(there are 12 occurrence of the vallue)

Expand|Select|Wrap|Line Numbers
  1.  
  2. select substr(Class_Data, instr(Class_Data,'Fee-Value'),30) from  School where class='S013' and grade='A' and date ='20130301';
  3.  
  4.  
Apr 25 '13 #1
3 2719
Rabbit
12,516 Expert Mod 8TB
That's because instr only finds the first occurrence. You will probably have to use a stored procedure because I can't think of a way to continually iterate through the string and create rows with a standard query.
Apr 25 '13 #2
@Rabbit: Thanks for replying..

I understood the that instr can not work, but the thing is in my environment using procedure is not allowed

only few select queries I can use in my project
Apr 26 '13 #3
Rabbit
12,516 Expert Mod 8TB
Without a stored procedure, it's next to impossible and very complex to accomplish. It's nigh impossible unless you know how many are in the string beforehand. And even if you know, it requires many embedded instr calls to pull them all out.
Apr 26 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by pankaj_wolfhunter | last post: by
reply views Thread by Jimm | last post: by
reply views Thread by Jimm | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.