Regex in calculated field

mike yates shared this question 3 months ago
Answered

I have a column called name that includes entries like Test!stuff!morestuff. I'd like to create a calculated field to include just Test (ie. regex to the first !) but not quite sure how to do this, i assume via freehand sql but open to suggestions ...

Comments (5)

photo
1

Hi Mike,

Thanks for reaching out. Indeed as you touched upon, and correctly speculated, the only way to accomplish would be via a Freehand SQL Calculated Field as you'll have to utilize a DB-specific Function in order to do this, which are only available via Freehand. Please let me know if you have any further questions on this.

Regards,

Mike

photo
1

Would you happen to have an example of how this would look in freehand sql ?

Mike Yates

Principal Solutions Engineer

Digital Service and Operations, NA East

Direct

+1 331-777-8742

Mobile

+1 815 557 4512

Clermont,

Florida

USA 34714

f4f29b43b2e4b0edcf560d34447d4327

From: Yellowfin Support <support@yellowfin.bi>

Sent: Thursday, April 23, 2020 11:06 AM

To: Yates, Mike <Mike_Yates@bmc.com>

Subject: [EXTERNAL] New Comment in "Regex in calculated field"

photo
1

Hi Mike,

I don't have any specific examples, but there are examples online, such as this one for SQL Server or this one for MySQL. There are several options to get this result and will depend on your RDBMS and what you're looking to return.

The following is an example I came up with to return all results before 'L' using MySQL.

My query:

/09c0cf939473d8c2f45057a2203b3686

Result:

/c045f20578b15ce19e816fef09dd24c7

You can see the original data in the left-most column, and the string before 'l' in the second column. My DB isn't case sensitive so there's no difference between 'l' vs. 'L', so that may be something else to look for on your end as well, though this wouldn't be applicable looking for '!' of course.

Please let me know how goes and whether you have any additional questions.

Regards,

Mike

photo
1

So I did get it to work … Backend database is MSSQL and this is what I cam up with to get the result I needed …

REPLACE(LEFT(full_inst_name, (CHARINDEX('!', full_inst_name))),'!','')

Thanks for the pointers …

Mike Yates

Principal Solutions Engineer

Digital Service and Operations, NA East

Direct

+1 331-777-8742

Mobile

+1 815 557 4512

Clermont,

Florida

USA 34714

b7cf95aee31d861c8cdf9a0dfcdcc17a

From: Yellowfin Support <support@yellowfin.bi>

Sent: Thursday, April 23, 2020 11:45 AM

To: Yates, Mike <Mike_Yates@bmc.com>

Subject: [EXTERNAL] New Comment in "Regex in calculated field"

photo
1

Hi Mike,

Awesome! You're welcome. This considered, I'll go ahead and close this case out then, but please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike