SQLTeam.com | Weblogs | Forums

Removing last charater when its a letter

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

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.

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)