Spark SQL: Extract String before a certain character-CodePudding

I have a string in a column in a table where I want to extract all the characters before the first dot(.). Here is the input:

Place
Chicago.City.XXXX
Denver.City.XXX.YYY
Dallas.City.FFF.DDD.GGGG

Expected output:

City
CHICAGO
DENVER
DALLAS

I am trying this:

select left(upper(Place), charindex('.', upper(Place))-1) as City from Table

But I am getting the error

Message : AnalysisException: "Undefined function: 'charindex'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'

CodePudding user response：

SparkSQL does not support charindex(). This should work:

select upper(split_part(Place, '.', 1)) as City from Table

CodePudding user response：

In Spark SQL there's substring_index

select substring_index(upper(Place), '.', 1) as City from Table