Replicating CHARINDEX() in Alteryx

Question

Hi all,

Is there any similar string function in alteryx that gives the same functionality as CHARINDEX(Substring, start, length) which is in SQL server. Actually I have SQL string functions where I need to replicate them into Alteryx.

I tried using Substring([ADDR_LINE_2], FindString([ADDR_LINE_2] ',') , 0). But, it doesn't work. Kindly please help me with the two below sql code replication into Alteryx.

1) CHARINDEX(',', SAP.[ADDR_LINE_2], 0)

2) CHARINDEX(',', SAP.[ADDR_LINE_2], CHARINDEX(',', SAP.[ADDR_LINE_2], CHARINDEX(',', SAP.

[ADDR_LINE_2], 0) + 1) + 1)

Thanks in advance.

Kind Regards,

Sreenivasa Teja.

GiuseppeC · Answer

Hi @stj1120,

as I mentioned in my previous post, Alteryx's implementation of the FindString function is 0-based. This means that the first character position is position 0, the second character position is position 1, and so on.

If the target is not found within the string, the function will return -1.

Although this might differ from SQL, it's very common in many programming languages.

Having said that, to mimic SQL's behaviour, you just need to add +1 to Alteryx's results.

Hope this helps,

Giuseppe

stj1120 · Answer

Hi @GiuseppeC

Thank you for your response.

However, please check the two below screen shots and explain me why there is a difference in the values of the position of the occurance of a string between Alteryx and SQL Server.

Normally in both tools, the first letter starts with the position number 1. But  why is it taking -1 value for the both if occurance of a string (target) is not found and also for the first position as well.

But, if you see the below image it is taking the first position as 0.

I'm little confused. I need the exact SQL Server output in Alteryx for the mentioned SQL queries in the actual post. Kindly look into this and help me out.

Awaiting your response.

Thanks in advance.

Kind Regards,

Sreenivasa Teja.

GiuseppeC · Answer

Hi @stj1120,

the FindString(string, target) function in Alteryx should give you what you need.

FindString(String,Target): Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string.

Returns the 0 based index of the first occurrence of Target in String. Returns -1 if no occurrence.

To implement your second function, you can then use the Substring function to remove the first part of the address up until the first occurrence of ",".

See the example attached. I've done it in multiple steps for clarity, but you could combine the functions if you wanted to.

Hope this helps!

Giuseppe

findstring-substring.yxmd