Extracting string after and before a character '-' or '<'

Question

Hello Everyone!!

 

Please Review Following table.

 

UDF_WorkLocation_6

------------------------------------------------- 

MUMBAI - Maharastra<6>

AHMEDABAD - Gujarat<1>

MUMBAI - Maharastra<6>

CHANDIGARH - Chandigarh<34>

NULL

DELHI - Delhi<42>

NULL

INDORE - Madhya Pradesh<56>

 

 

Here it is column name 'UDF_WorkLocation_6' in table.I want split string from '-' then set into two columns And In second column Remove string after charter '<' using SQL Query. I want output in two columns as below.

 

Output Table

Column1	Column2
MUMBAI	Maharastra
AHMEDABAD	Gujarat
MUMBAI	Maharastra
CHANDIGARH	Chandigarh
NULL	NULL
DELHI	Delhi
NULL	NULL

 

Can anyone please help me?

 

What I have tried:

 

I have prepared query for split string.For instant, string is 'MUMBAI - Maharastra<6> '

 

and got output for second column like 'Maharastra<6>' using below sql query

 

SELECT LTRIM(SUBSTRING(AccountMasterUDF.UDF_WorkLocation_6, CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) + 2, 100))

 

But i also want two remove charters after '<' this.