Search code examples
sqlsubstringcharindex

How to select a substring but leaving out the last character if is an alphabet in SQL

DECLARE @CUSTOMER_ID VARCHAR(50)
SELECT @CUSTOMER_ID = 'RS/123/2017(SA)' 
PRINT @CUSTOMER_ID

DECLARE @CUSTOMER_ID1 VARCHAR(50)
SELECT @CUSTOMER_ID1 = 'RS/123/2017SA' 
PRINT @CUSTOMER_ID1

I need a function that can return my customer_ID except for the last characters in the string. I can only work on the first one using the below function.

case when charindex('(',@CUSTOMER_ID) = 0 then @CUSTOMER_ID 
else left(@CUSTOMER_ID,charindex('(',@CUSTOMER_ID)-1))
Solution

  • Find a position of the rightmost digit and take left symbols of the string including this position

    DECLARE @CUSTOMER_ID1 VARCHAR(50);
SELECT @CUSTOMER_ID1 = 'RS/123/2017SA';

select left(@CUSTOMER_ID1, len(@CUSTOMER_ID1) - patindex('%[0-9]%', reverse(trim(@CUSTOMER_ID1))) + 1) customer_ID;