Post reply

Is there an easy way to do this?

  • August 22, 2007 at 9:39 am

    #373293

    Hi All,

    For reporting purposes I need to be able to strip a portion of text out of a text field.

    There is no uniform pattern to the text field, however I need to include only the text after the 2nd last ~ (if one exists that is).

    Note that many fields have several ~'s in them and then there are some that don't at all.

    I do have a solution to this but it is quite long winded and includes several replaces and the charindex function.

    Thanks for your help.

  • August 22, 2007 at 9:41 am

    #728611

    Can you give an example of what the column data might look like?

  • August 22, 2007 at 9:44 am

    #728615

    Sure, here is a small cross section

     

    Re Bill 177959 11/04/07

    Re Bill 177959a 14/05/07

    HMCS 54BNHDCQHATTONA~OP~HMCS - text written here (Re: 62637373739/89272)

    HMCS #01BNHDCQpatteml~OP~HMCS - text written here (Re: 62637373739/89272)

    HMCS #01BNHDCQCookeA~OP~HMCS – text written here (Re: 62637373739/89272)

  • August 22, 2007 at 11:43 am

    #728661

    OK, this is a corrected version of the query, which works with 0, 1, 2, or more ~s in the column. The select returns everything after the second to last ~ for those records where there are 2 or more ~s.

    SELECT right(source,

       patindex('%~%',

       substring(reverse(source), patindex('%~%', reverse(source)) + 1 , len(source))) +

       patindex('%~%', reverse(source)) -1 ) as result

    from SourceTable

    where patindex('%~%%%~%', reverse(source)) > 0

    There might be an easier way...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply