SQLTeam.com | Weblogs | Forums

Removing last charater when its a letter


#1

Hi

I want to remove the last character of a field in ssis. I only want to remove it if its a letter.

eg

[code]
240261L should be 240261
013022EG should be 013022
and if it comes out like this
18930 I want that to stay the same.

Anyone any ideas


#2

Unfortunately there does not seem to be a function like ISALPHA or ISNUMERIC in SSIS. The only way I know of is to use a script component. See an example here

PS: My information is a little dated. I don't know if newer versions have this ability.


#3

In standard SQL syntax, you would do this for "the last character" (as you stated). For multiple letters at the end, you'd need additional logic.

STUFF(column_name, LEN(column_name), CASE WHEN RIGHT(column_name, 1) LIKE '[A-Z]' THEN 1 ELSE 0 END, '')

For example:
select stuff(column_name, LEN(column_name), CASE WHEN RIGHT(column_name, 1) LIKE '[A-Z]' THEN 1 ELSE 0 END, '')
from (values('240261L'),('013022EG'),('18930')) as test_data(column_name)