0

I have table with one column 'UDF_WorkLocation_6' which contain string.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' which contain string in table such as 'INDORE - Madhya Pradesh<56>'.

I want split string from '-' then show output into two columns,In First Column getting string before '-' like 'INDORE' from 'INDORE - Madhya Pradesh<56>' string & second column getting string after '-' and also remove charter after '<'this like Madhya Pradesh from 'INDORE - Madhya Pradesh<56>'.

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<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 and query for getting string 'MUMBAI' in first column.

Can anyone please help me to prepared query without split function?

1

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('<',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
        (
            '<',
            LTRIM
            (
                RIGHT
                (
                    AccountMasterUDF.UDF_WorkLocation_6, 
                    LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)
                )
            )
        ) - 1
    ) AS Column2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.