CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6662

SQL Query/function to remove alphabets only from end

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?

Upvotes: 3

Views: 491

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

The simplest method is

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

Upvotes: 1

ughai
ughai

Reputation: 9880

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

Upvotes: 3

Related Questions