Search the Community

I am trying to process a string which contains various data relating to an elearning record; Moodle SCORM suspend_data if you're familiar with it Since I do not have access to server side SQL/CLR functions through the Moodle interface, I am attempting to reference, and extract, the relevant information from the string by explicitly referencing the character locations of the information I need in the string and then converting them as required so they can be summed e.g. SELECT prefix_user.username,........ ((CASE(SUBSTRING(value_rep,237,1)) WHEN 'c' THEN 1 ELSE 0 END) + ........(CASE(SUBSTRING(value_rep,3659,1)) WHEN 'c' THEN 1 ELSE 0 END)) AS "# Videos watched"........FROM ((prefix_user LEFT JOIN (SELECT prefix_scorm_scoes_track.userid, Replace(value,"''","'XX'") AS value_repFROM prefix_scorm_scoes_track WHERE (((prefix_scorm_scoes_track.element)='cmi.suspend_data'))) AS vid ON prefix_user.id = vid.userid The string contains multiple occurrences of the string patterns '','n' and 'nn' where n is any digit 0-9 which I need to replace with the same string pattern e.g. 'XX', so that the total length of the string as a whole remains the same. I Initially thought that the pattern would always exist as '' or 'nn' so it was relatively simple to use a REPLACE(value,"''","XX") I understand it's not possible to use regex in the REPLACE function and have been investigating the use of PATINDEX with STUFF, but understand this is inly suitable for operating on 1 occurrence of a pattern. The report can be output to a CSV for viewing in Excel, so my conclusion is currently to carry out post-processing with Excel VBA where I will have access to loops and other functions. Is what I am trying to achieve possible without the use of a function on the SQL server?