Find a character index in string in spark sql

user_2841795 0
Viewed 18462

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

pyspark-sql databricks apache-spark-sql
1 Answers

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
user_7503760
Related