SQL Query/function to remove alphabets only from end

Question

Need help in creating a function which removes characters(alphabets) only from end or till a number comes at the right.

For Eg :

select fnStripRightAlpha('ABCD123F') --Should Return 'ABCD123'

select fnStripRightAlpha('PORT123G67KK') --Should Return 'PORT123G67'

select fnStripRightAlpha('123465') --Should Return '123465'

select fnStripRightAlpha('ABCDG') --Should Return ''

I saw functions which remove all alphabets, but they do not solve my purpose as only rightmost characters are to be stripped.

Any Ideas?

Answer 1

Assuming you only have alphanumeric characters, You can use PATINDEX with STUFF and REVERSE like this.

Query

SELECT
ISNULL(REVERSE(STUFF(REVERSE(col),1,PATINDEX('%[0-9]%',REVERSE(col)) -1,'')),'') as col
FROM
(
    VALUES('ABCD123F'),('PORT123G67KK'),('123465'),('ABCDG')
) as tab(col)

OUTPUT

col
ABCD123
PORT123G67
123465
''