RIGHT() function returning blank result I have a single column that contains information that I need to have split up into three separate columns. I currently have a SELECT statement that works partially, but my ChangeTo column is not returning any data and I cannot figure for the life of me why. Here is what the current column being returned looks like:

| bp_comment                                             |
----------------------------------------------------------
| changed status from "new" to "in progress"             |
| changed ORT status from "in progress" to "Code Review" |
| changed MileStone from "1.1" to "1.2"                  |


Here are how I need the results to return:

| ChangeType              |  ChangeFrom  |   ChangeTo  |
-----------------------------------------------------
| changed status from     | new          | in progress |
| changed ORT status from | in progress  | Code Review |
| changed MileStone from  | 1.1          | 1.2         |


Here are how the results are returning right now:

| ChangeType              |  ChangeFrom  |   ChangeTo  |
-----------------------------------------------------
| changed status from     | new          |             |
| changed ORT status from | in progress  |             |
| changed MileStone from  | 1.1          |             |


Here is the query I am using:

SELECT
    LEFT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5) AS 'ChangeType'
    ,REPLACE(SUBSTRING(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5, CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - 5), '"', '') AS 'ChangeFrom'
    ,REPLACE(RIGHT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' to ', REVERSE(CONVERT(VARCHAR(MAX), bp.bp_comment)))), '"', '') AS 'ChangeTo'
FROM
    bug_posts bp


Thank you in advance!
The REVERSE is clearing our your string, not entirely sure why. 

EDIT: Here's why. In this sequence the string ' to ' is actually ' ot '. (Ken sniped it mid edit!)

Fixed:

SELECT
LEFT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5) AS 'ChangeType'
,REPLACE(SUBSTRING(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5, CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - 5), '"', '') AS 'ChangeFrom'
,REPLACE(RIGHT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' ot ', REVERSE(CONVERT(VARCHAR(MAX), bp.bp_comment)))), '"', '') AS 'ChangeTo'
FROM
bug_posts bp


Here's an alternative approach.

SELECT LEFT( CONVERT( varchar( max ) , bp.bp_comment ) , CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                   + 5 )AS 'ChangeType' , 
   REPLACE( SUBSTRING( CONVERT( varchar( max ) , bp.bp_comment ) , CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                 + 5 , CHARINDEX( ' to ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                     - CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                     - 5 ) , '"' , '' )AS 'ChangeFrom' , 
   REPLACE(REPLACE(RIGHT(bp.bp_comment, LEN(bp.bp_comment) - CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment))), '"', ''), 'to ', '') AS 'ChangeTo'
 FROM bug_posts bp;