Convert datatype in a BeastMode

Canio
Canio Member
in Beast Mode

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>

image.png

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

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ArborRose
    ArborRose Coach

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Canio
    Canio Member
    edited February 2024

    Bang! Trim() worked

    ty

Categories