SQL SERVER CHARINDEX Function

Question

I have a scenario WHERE the string I am searching for is located in two different location on one column of my table.
Example String Data:

LoanSuffix
502103103
186210104
5062854
16836-10
24753-1
72982-2

I would like to be able to extract 6 characters to the left of "103", "104", "-10", "-1", "-2" or 6 characters of "5062854"

I have the following code and it seems to work fine except when I have character 103 in multiple string such as 502103103.

,CASE WHEN CHARINDEX('-', LoanSuffix) > 0 THEN RTRIM(LEFT(LoanSuffix, CHARINDEX('-', LoanSuffix) - 1))
               WHEN CHARINDEX('101', LoanSuffix) > 0 THEN RTRIM(LEFT(LoanSuffix, CHARINDEX('101', LoanSuffix) - 1))
               WHEN CHARINDEX('102', LoanSuffix) > 0 THEN RTRIM(LEFT(LoanSuffix, CHARINDEX('102', LoanSuffix) - 1))
               WHEN CHARINDEX('103', LoanSuffix) > 0 THEN RTRIM(LEFT(LoanSuffix, CHARINDEX('103', LoanSuffix) - 1))
               WHEN CHARINDEX('104', LoanSuffix) > 0 THEN RTRIM(LEFT(LoanSuffix, CHARINDEX('104', LoanSuffix) - 1))
               ELSE LoanSuffix
END AccountNumber

For LoanSuffix of 502103103, the result of the above CASE WHEN gives me 502. But the ideal result should be 502103.

Thank you in advance for your help.

Answer 1

If the suffix is always at the end, then try this approach:

. . .
when right(rtrim(LoanSuffix), 3) = '103' then left(LoanSuffix, len(LoanSuffix) - 3)
. . .

Use it in other cases too.