SQL SERVER CHARINDEX Function

Villa 186 Reputation points
2021-03-03T18:53:42.763+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
{count} votes

Accepted answer
  1. Viorel 113.7K Reputation points
    2021-03-03T19:24:13.577+00:00

    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.


0 additional answers

Sort by: Most helpful