CODEWITHSUNDEEP
sqlsql-servertrim
Taz
Taz

Reputation: 169

sql trimming function

I have a table called transactions which has a column Customer_Transactions_ID

Now the values in this column vary in the below

AB_EL_205_72330_H

AB_EL_820_23066_E_N

AB_EL_820_23066

I want to trim all the values that start with "AB_EL" to make them from

AB_EL_820_23066_E_N (or whatever variation of the above) to
AB_EL_820_23066

So basically no E or H after the id's

Is this possible?

Upvotes: 0

Views: 83

Answers (2)

Anil
Anil

Reputation: 138

if i understand your requirement correctly.if text starts with ab_el then trim _e,_l from the text. 

DECLARE @txt VARCHAR(100)= 'aqb_el_205_72330_h';

SELECT ISNULL('ab_el'+CASE WHEN @txt LIKE 'ab_el%' THEN replace(replace(REPLACE(@txt,'ab_el',''), '_e', ''), '_h', '') END,@txt);

Upvotes: 1

EzLo
EzLo

Reputation: 14189

This will give your values truncated until the last ocurrence of a number.

SELECT
    Original = T.Customer_Transactions_ID,
    Truncated = SUBSTRING(
        T.Customer_Transactions_ID,
        1,
        LEN(T.Customer_Transactions_ID) - PATINDEX('%[0-9]%', REVERSE(T.Customer_Transactions_ID)) + 1)
FROM
    Transactions AS T

Upvotes: 1

Related Questions