Spark SQL equivalent for Split_Part()

user_13252380 0
Viewed 1029

I am trying to get the equivalent for

split_part(split_part(to_id, '_', 1), '|', 3)

in Spark SQL

Can anyone please help

SELECT
to_id
,split(to_id,'_')[1] AS marketplace_id
,from_id
,split(split(to_id, '_')[0], '|')[2] AS asin
--,split(to_id, '|')[2] AS asin
FROM DDD

Context:

to_id = ASIN|CATALOG|B0896YZABL_7 
expected = B0896YZABL 
current output = |
amazon-redshift apache-spark-sql sql
1 Answers

You have to escape the vertical bar char | like this \\|.

Below is a simple Scala example, you can try it in the interactive Scala shell:

val s = "ASIN|CATALOG|B0896YZABL_7"
val result = s.split("\\|") // Will be Array(ASIN, CATALOG, B0896YZABL_7)
print(result.last) // Prints 'B0896YZABL_7'

In your case, it should be:

SELECT
to_id
,split(to_id,'_')[1] AS marketplace_id
,from_id
,split(split(to_id, '_')[0], '\\|')[2] AS asin
--,split(to_id, '\\|')[2] AS asin
FROM DDD
user_2418990
Related