Using MID function with Workbench 3?

redbull

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!

 

 

kshah008

Hi all,

 

Is anybody able to help @redbull with their question?

Thanks!

SeanPT

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

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

redbull

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

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

---

@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