Using Left() or Substring() IN-DB Formula

[grandepaci]

Hi everyone,

i want to get the first 50 letters of my "TEXT" column to avoid long text.

Here i want to use an IN-DB Formula Tool.

I tried left("TEXT",50)  aswell as Substring("TEXT",0,50). Both of those ideas gave an error.

The formula does work with a normal formula Tool, but i have to find a solution with the IN-DB version for future plans.

Does anyone have another idea?

We are working with Oracle Databases.

Sincerely

Gabriel

[IraWatt]

Hey @grandepaci,

In SQL Substring is indexed from 1 so maybe try Substring("TEXT",1,50) or Substring('TEXT',0,50). What does the error say?

SQL SUBSTRING: Extract a Substring from a String (sqltutorial.org)

[DataNath]

Don't you need to write it out in SQL form, rather than the standard syntax we use in workflows? I.e. front it with SELECT and define the table:

SELECT LEFT (column, 50) FROM table

[grandepaci]

@irawatt

i tried that aswell and it was the same error.

Error: Formula In-DB (35): DataWrapOCI: Unable to prepare the query: "WITH "Tool22_c105" AS (SELECT...

this is the same error i get when typing in wrong querys

@DataNath

i understand the idea behind referencing it directly from the table. Unfortunately, i got the same error

[DataNath]

@grandepaci are you able to show the full error and perhaps a screenshot of your In-DB formula tool configuration? Thanks

[kuoshihyang · Accepted solution]

Try streaming your data out using a Data Stream Out tool to the workflow, pass the data into a regular formula tool with your Left and Substring functions then stream the data back after the data is transformed and cleansed using Data Stream In if you need to pass it back to your in-db database call.  

[DSzivropulosz]

Hi There,

I would have done it this way:

[Magneto_]

@DSzivropulosz I think he already tried that

[apathetichell]

https://stackoverflow.com/questions/11309247/left-function-in-oracle#:~:text=LEFT%20is%20not%20a%20f....

functions in formula db must be used in the db's native sql. 

SUBSTR would have worked.