1

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!

  • What do you expect to happen for changed status from "A from Z" to "A to Z"? (In other words, your code assumes that there will only be one to and one from in the string. This is part of the reason you should consider separate columns for the object, from, and to data, instead of parsing sentences, which is not the database's job.) – Aaron Bertrand Jun 1 '15 at 16:34
2

Simple enough. When you used the reverse you continued to search for ' TO '. If you switch it to ' OT ' then it works fine. FYI I'm not sure why you have all of the CONVERTs in there but you probably don't need them.

 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

FYI I'm not sure why you have all of the CONVERTs in there but you probably don't need them.

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

If you do find that you need them put something in the comments along with what version of SQL you are using and I may be able to simplify things for you somewhat.

EDIT: I'm pretty sure this will work 2005 and up and it simplifies the code somewhat. Might be helpful if your query is quite a bit more complicated than what you are showing. Basically I'm pushing the CONVERT and REPLACE and the CHARINDEX(' from ') into a CROSS APPLY. In both cases you are using the functions multiple times and this way you can run them once and just call them as needed. Also this means that if you need to change something you only have to do so in one place.

SELECT
    LEFT(vars.bp_comment, vars.CharIndex_From + 5) AS 'ChangeType'
    ,SUBSTRING(vars.bp_comment, vars.CharIndex_From + 5, CHARINDEX(' to ', vars.bp_comment) - vars.CharIndex_From - 5) AS 'ChangeFrom'
    ,RIGHT(vars.bp_comment, CHARINDEX(' ot ', REVERSE(vars.bp_comment))) AS 'ChangeTo'
FROM
    bug_posts bp
CROSS APPLY (SELECT REPLACE(CONVERT(varchar(max),bp.bp_comment),'"','') AS bp_comment,
            CHARINDEX(' from ', bp.bp_comment) AS CharIndex_From
            /*Not putting the CHARINDEX(' to ') because it's only used once & requires the REPLACE */
            ) vars
  • Bingo, that's what it was! With regards to the CONVERT() function, I need to do it as is an older database using an NTEXT data type. Otherwise I get arguments. I'm also going to take a closer look at the second solution that you've provided - I've not delved much into using the Cross Apply, but this seems like a perfect application for it. Thank you for your assistance! – PicoDeGallo Jun 1 '15 at 17:19
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.