Using MID function with Workbench 3?

redbull
redbull Member
in Archive

I need to extract the "Pushing or Pulling" expression from the string below:

 

Strain or Injury By: Pushing or Pulling

 

When I try to use the MID function, I know how to identify the text and the starting location, but I'm having trouble expressing how to dynamically capture the rest of the text following the colon.  If I express an actual number, it triggers an error for any fields that don't have that many characters following the colon.  If I leave the expression blank, 

 

mid(AR_CAUSE_OF_INJURY,SEARCH(":",AR_CAUSE_OF_INJURY)+2,)

 

I appreciate any help!

 

 

Comments

  • kshah008
    kshah008 Contributor

    Hi all,

     

    Is anybody able to help @redbull with their question?

    Thanks!

  • SeanPT
    SeanPT Contributor

    In TSQL you could use CASE on the column to search for the strings Pushing or Pulling and return just that string. But I'm not sure if you can use that with ODBC in WB3 or if you are even pulling from a TSQL table.


    Sorry if that isn't much help ?

  • kshah008
    kshah008 Contributor

    @redbull, did SeanPT's response help address your issue?

  • redbull
    redbull Member

    Hey kshah008!  Nope, the issue remains.  I just can't seem to get this MID function figured out.  Creating a CASE scenario doesn't make sense, because the verbiage following the colon (Accident: Fall) is dynamic.  I'm not a SQL Jedi, so that doesn't help the situation. ?

     

    Thanks for following up!

  • Mark1
    Mark1 Member

    Pls try below fomula:

     

    RIGHT( 'Strain or Injury By: Pushing or Pulling', LENGTH('Strain or Injury By: Pushing or Pulling') -locate(':','Strain or Injury By: Pushing or Pulling'))

  • kshah008
    kshah008 Contributor
    @redbull wrote:

    Hey kshah008!  Nope, the issue remains.  I just can't seem to get this MID function figured out.  Creating a CASE scenario doesn't make sense, because the verbiage following the colon (Accident: Fall) is dynamic.  I'm not a SQL Jedi, so that doesn't help the situation. ?

     

    Thanks for following up!

    No problem! Tagging you again to check out Mark1's latest reply. @redbull

This discussion has been closed.

Categories