Convert datatype in a BeastMode

Canio

I have data coming in from a sql table, and I cannot change the datatype of it at the source.

I'm trying to join on the columns but it won't join on certain rows of data b/c One column is varchar25 and the other is nchar10 from the SQL Server side it comes from.

They are already string, so when I use the Magic Tile to convert the datatype to a string, it obviously doesn't do anything.

I also tried to change the formula to string(Event_ID) and then in the Magic tile changed it to "dictionary" and that didn't work either>

I also tried a regex bit that doesn't seem to have worked either:

REGEXP_REPLACE(EventID, '.{11,}', '')

Any assistance would be great.

Or, are you gonna tell me I need to go into Fusion and do a MySQL edit of the data that way? which I don't mind doing…but trying to see if a beast mode/formula is an option too

GrantSmith

Do the string values look the same when you compare them? Have you done a TRIM on the IDs to remove any trailing spaces? Are they different lengths between the two databases?

GrantSmith

Do the string values look the same when you compare them? Have you done a TRIM on the IDs to remove any trailing spaces? Are they different lengths between the two databases?

ArborRose

Can you cast the varchar(10) to be varchar(25)?

SELECT t1.EventID, t2.OtherColumn
FROM Table1 t1
JOIN Table2 t2
ON CAST(t1.EventID AS VARCHAR(25)) = t2.OtherColumn

Canio

Bang! Trim() worked

ty