Jump to content
Sign in to follow this  
Cheeesenbranston

Replace multiple instances of multiple string patterns using SQL and no server function

Recommended Posts

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...