Extracting string after and before a character '-' or '<' using Sql Query I have table with one column 'UDF_WorkLocation_6' which contain string.Please Review following table.
UDF_WorkLocation_6
MUMBAI - Maharastra&lt;6&gt;
AHMEDABAD - Gujarat&lt;1&gt;
MUMBAI - Maharastra&lt;6&gt;
CHANDIGARH - Chandigarh&lt;34&gt;
NULL
DELHI - Delhi&lt;42&gt;
NULL
INDORE - Madhya Pradesh&lt;56&gt;

Here it is column name 'UDF_WorkLocation_6' which contain string in table such as 'INDORE - Madhya Pradesh&lt;56&gt;'.
I want split string from '-' then show output into two columns,In First Column getting string before '-' like 'INDORE' from 'INDORE - Madhya Pradesh&lt;56&gt;' string &amp; second column getting string  after '-' and also remove charter after '&lt;'this like Madhya Pradesh from 'INDORE - Madhya Pradesh&lt;56&gt;'.
So output in table like,
**Output Table**

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

I have tried to prepared query for split string.
For instant, string is 'MUMBAI - Maharastra&lt;6&gt; ' and got output for second column like 'Maharastra&lt;6&gt;' 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 '&lt;' this and query for getting string 'MUMBAI' in first column.
Can anyone please help me to prepared query without split function?
The LEFT and RIGHT functions are helpful for this kind of parsing. They only return X number of characters from that side of a VARCHAR like so:
SELECT RTRIM(LEFT(AccountMasterUDF.UDF_WorkLocation_6, CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) - 1)) AS Column1,  
LEFT(LTRIM(RIGHT(AccountMasterUDF.UDF_WorkLocation_6, LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6))), CHARINDEX('&lt;',LTRIM(RIGHT(AccountMasterUDF.UDF_WorkLocation_6, LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)))) - 1) AS Column2

As you can see, combined with CHARINDEX to locate the - in both cases, and LTRIM and RTRIM to remove any unneeded whitespace.
In the second case you need to apply the LEFT function on top of your output from the RIGHT function to do one more search and removal of the ending .
Also here's a more formatted version of the stacked functions above that I find more readable if you prefer:
SELECT 
    RTRIM
    (
        LEFT
        (
            AccountMasterUDF.UDF_WorkLocation_6, 
            CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) - 1
        )
    ) AS Column1,  
    LEFT
    (
        LTRIM
        (
            RIGHT
            (
                AccountMasterUDF.UDF_WorkLocation_6, 
                LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)
            )
        ), 
        CHARINDEX
        (
            '&lt;',
            LTRIM
            (
                RIGHT
                (
                    AccountMasterUDF.UDF_WorkLocation_6, 
                    LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)
                )
            )
        ) - 1
    ) AS Column2