Fun with string functions
I do a lot of string functions to look at URLs. If I want to use a SUBSTRING and INSTR Function what's the best way to move the end a dynamic number of characters?
EXAMPLE: I want to show the string after the 1st '=' and before 'the_end' in the 'big_string' field.
I wrote:
SUBSTRING(`big_string`,INSTR(`big_string`,'=')+1,INSTR(`big_string`,'the-end')-INSTR(`big_string`,'='))
The above works but I'm getting an extra character at the end that preceeds 'the-end' that's different. I tried sticking -1 in a few different places and it broke. Thoughts?
Best Answer
-
Sure thing. So here is what I did to model your question:
I created this string (my garbage example) as a URL model of what I think you're looking for:
https://www.google.com?formula=yes&donewithstring=the-end
I then used your code example to parse it, and I think this will work the way you want (added in red below):
SUBSTRING(`big_string`,INSTR(`big_string`,'=')+1,(INSTR(`big_string`,'the-end')-1)-INSTR(`big_string`,'='))
2
Answers
-
MattRK - are you doing this string manipulation in a beastmode, Magic ETL, or SQL? My answer changes a bit depending on which environment you're in.
0 -
As a beast mode. In SQL it's less difficult.
0 -
Sure thing. So here is what I did to model your question:
I created this string (my garbage example) as a URL model of what I think you're looking for:
https://www.google.com?formula=yes&donewithstring=the-end
I then used your code example to parse it, and I think this will work the way you want (added in red below):
SUBSTRING(`big_string`,INSTR(`big_string`,'=')+1,(INSTR(`big_string`,'the-end')-1)-INSTR(`big_string`,'='))
2 -
That works great, this is a good way to repilcate a "MID" function until such time as one exists in a Beastmode.
1
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 562 Beast Mode
- 9 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive