How to use LEFT and RIGHT keyword in SPARK SQL

问题:

I am new to spark SQL,

In MS SQL, we have LEFT keyword, LEFT(Columnname,1) in('D','A') then 1 else 0.

How to implement the same in SPARK SQL. Kindly guide me

回答1:

You can use substring function with positive pos to take from the left:

import org.apache.spark.sql.functions.substring

substring(column, 0, 1)

and negative pos to take from the right:

substring(column, -1, 1)

回答2:

import org.apache.spark.sql.functions._  

Use substring(column, 0, 1) instead of LEFT function.

where

• 0 : starting position in the string
• 1 : Number of characters to be selected

Example : Consider a LEFT function :

LEFT(upper(SKU),2)

Corresponding SparkSQL statement would be :

substring(upper(SKU),1,2) 

回答3:

To build upon user6910411's answer, you can also use isin and then to build a new column with the result of your character comparison.

Final full code would look something like this

import org.apache.spark.sql.functions._

df.select(substring($"Columnname", 0, 1) as "ch")
    .withColumn("result", when($"ch".isin("D", "A"), 1).otherwise(0))