SQL Query / function to remove alphabets only from end

Question

SQL Query / function to remove alphabets only from end

Need help creating a function that removes characters (alphabets) only from the end or to the number on the right.

For example:

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

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

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

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

I see functions that remove all alphabets, but they do not solve my purpose as only the right-most characters should be removed.

Any ideas?

+3

sql sql-server sql-server-2008-r2

Abdul rehman sayed 06 Aug 15 at 12:26

source to share

2 answers

The easiest way -

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

+1

Madhivanan 06 Aug 15 at 12:40

source to share

ughai · Accepted Answer · 2015-08-06T12:32:19+0000

Assuming you only have alphanumeric characters, you can use PATINDEX

with STUFF

and REVERSE

as follows.

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
''

SQL Query / function to remove alphabets only from end

More articles: