question

DouangRintharamy-6826 avatar image
0 Votes"
DouangRintharamy-6826 asked ·

SQL SERVER CHARINDEX Function

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.

sql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes the loan suffix will always be at the end.

Thank you for your help!

0 Votes 0 ·