Sherman Sherman - 14 days ago 5
SQL Question

SQL Parsing Strings

Our Production Notes table and our Dev Notes table are now different.

In Production - System notes were being added into the column "The_Notes" and were identified living between two hash tags, i.e. "# system note blah, blah #".

Users want the System Notes in a different column from what they enter as user notes.

You know where this is going....

I have a stored procedure which runs on a daily job and each night it truncates the Dev tables and inserts what is in the Production tables.

I need a query that splits out the System notes from our Production table and inserts them into their own column.

Example of Production Table

Production Notes Table Columns & Row Samples

|Num | The_Notes
| 0 | Called
| 1 | # Claim created Dec 1, 2016 # # Need By Date Dec 24, 2016 # I Filed
| 2 | Call me
| 3 | # Claim created Dec 1, 2016 # Call me back
| 4 | # Claim created Dec 1, 2016 # # Need By Date Dec 24, 2016 #


Not all rows have system notes. Not all rows have user notes.

In C# this is a breeze for me, SQL is not my primary language, our SQL guy bailed.

Dev Table Results Should Be:

|Num | System_Notes |User Notes
| 0 | | Called
| 1 | # Claim created Dec 1, 2016 # # Need By Date Dec 24, 2016 # |I Filed
| 2 | |Call me
| 3 | # Claim created Dec 1, 2016 # |Call me
| 4 | # Claim created Dec 1, 2016 # # Need By Date Dec 24, 2016 #


I was hoping to do this by counting the # tags to see if they were even number and doing an if substring and replace

Answer

Try below query

select num, 
       case 
         when charindex('#', notes) > 0 
           then reverse(SUBSTRING(reverse(notes), CHARINDEX('#', reverse(notes)), len(notes))) 
         else '' 
       end system_notes,
       case 
         when charindex('#', notes) > 0 
           then reverse(SUBSTRING(reverse(notes), 0, CHARINDEX('#', reverse(notes)))) 
         else notes 
       end User_notes
   from table1;

The above query returning below result

enter image description here