Extracting dollar value from h_detail_info field in wf_history view

Client needs to extract the dollar amount from h_detail_info field in wf_history view.

• Munis 2018.1 and later versions

• SSRS reports

• Crystal reports

• SaaS, Tyler-hosted clients

• OnPrem, Self-hosted clients

• Work Flow History

The field h_detail_info contains multiple substrings separated by a TAB or space. Each substring represents a different part of data. The location of dollar amount within the field varies based on process code.

SQL expression for extracting the dollar amount present at the beginning of the second substring:

LEFT(ltrim(REPLACE(h_detail_info, LEFT(h_detail_info, CHARINDEX(' ', h_detail_info)), '')), CHARINDEX(' ', ltrim(REPLACE(h_detail_info, LEFT(h_detail_info, CHARINDEX(' ', h_detail_info)), ''))))

SQL expression for extracting the dollar amount at the end:

right(rtrim(h_detail_info ),charindex(' ',reverse(rtrim(h_detail_info))+' ')-1)