3

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?

Community
  • 1
  • 1
Abdul Rehman Sayed
  • 6,532
  • 7
  • 45
  • 74

2 Answers2

3

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
''
ughai
  • 9,830
  • 3
  • 29
  • 47
1

The simplest method is

    declare @str varchar(100)
set @str='ABCD123F'
select substring(@str,1,len(@str)-patindex('%[0-9]%',reverse(@str))+1)
Madhivanan
  • 13,470
  • 1
  • 24
  • 29