Splitting by delimiter vs LOCATE() function equivalent

NateBI

Hi All,

I want to fill the nulls in column A with the values in column B (or C if B is null).

A case statement did not work here so I am aiming to achieve this by combining the columns and then separating them by delimiter. Unfortunately, by separating them we are - of course- back to square one.

I was able to use this method for dates because I could do a LEFT(Date_shared,10) and extract the first date in the merge. However, for IDs, the first ID could be a length between 4 and 14.

It's a simple LEFT(FIND()) or LOCATE() sql situation but there doesn't seem to be a 'find position' function in the Magic ETL

All help appreciated

ST_-Superman-_

INSTR() will return the location of the first instance of a string

NateBI

Hi @ST_-Superman-_ Thank you - I thought this was a Beast Mode only function. Plus SUBSTRING for the reverse - using position to extract from a string. Thanks!

jaeW_at_Onyx

use COALESCE() function

NateBI

@jaeW_at_Onyx thank you. This would have been a real time saver 🙃