ADD COLUMN IN DATABASE (IN-DB)

[Leo_ddantas]

Hi, guys!

I'm trying to add a new column in db table, but no success until now... this is the problem:

I have a column called "COD_EMPR" and a I need to create a column using the characters after the delimiter... For example:

COD_EMPR = 0004_0341

How can I create a column to get only the characters "0341"?

[MilindG]

Not too familiar with In-DB but have you tried using formula and regex

[Leo_ddantas]

Yes, I tried and it worked well.. but I need to do this using the in-db formula tool:

the problem is that it's different from formula tool out of in-db and I don't know exactly how to write the expression needed..

[Felipe_Ribeir0]

Hi @Leo_ddantas 

It depends on which database you are using. You will use the formula in-db tool to do that, but the syntax to write the function will be different for each one. 

If you are talking about SQL Server, you can use this:

For New1 column: substring("COD_EMPR", 1, charindex('_', "COD_EMPR")-1)

For New2 column: substring("COD_EMPR", charindex('_', "COD_EMPR")+1, LEN("COD_EMPR"))

To deal with In DB formulas, search what is the syntax to do what you need to do for the database that you are using, and then put it into Alteryx.

[Leo_ddantas]

Hi, Felipe! Hmm.. it didn't work :(

When I use the expression you recommended, I receive the following error:

Error: Formula In-DB (25): Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database](-3706)Syntax error: expected something between '(' and the string '_'.

I'm using Teradata Database...

I tried using function RIGHT and it worked, the formula tool didn't return error but when I tried to visualize the final table with browse tool, I received this error:

Error: Browse In-DB (26): Error SQLExecute: [Teradata][ODBC Teradata Driver][Teradata Database](-3669)More than one value was returned by a subquery.

My expression, using RIGHT, is this one: 

SELECT RIGHT ("COD_EMPR",4) FROM mytable

[Felipe_Ribeir0]

Hi @Leo_ddantas 

Yes, my example was for SQL Server, Teradata has a different syntax.

I dont have Teradata here to test, but you could try the STRTOK function STRTOK("COD_EMPR", "_", 2):

https://www.revisitclass.com/teradata/how-to-split-the-string-based-on-the-delimiter/