Find a character index in string in spark sql

Question

I am SQL person and new to Spark SQL

I need to find the position of character index '-' is in the string if there is then i need to put the fix length of the character otherwise length zero

string name = 'john-smith'

if '-' is in character position 4 then 10 otherwise length 0

I have done in SQL Server but now need to do in Spark SQL.

select 
case 
when charindex('-', name) = 4 then 10
else 0 
end 

I tried in Spark SQL but failed to get results.

select find_in_set('-',name) 

Please help. Thanks

Solution

You can use instr function as shown next. insrt checks if the second str argument is part of the first one, if so it returns its index starting from 1.

//first create a temporary view if you don't have one already
df.createOrReplaceTempView("temp_table")

//then use instr to check if the name contains the - char
spark.sql("select if(instr(name, '-') = 4, 10, 0) from temp_table")

The arguments for the if statement are:

• instr(name, '-') = 4 condition to check
• 10 result for valid condition
• 0 result for false condition

Comments

[user2841795]

Perfect all sorted. There is no need to create temp table (may be for me) instr function works perfectly. Thank you @Alexandros.

[RobertoST]

Is there a way this could be done in pyspark?