SQL how to remove the last character of a string if it is not numeric?-CodePudding

PhoneNumber
1111111
1111111name
1111111ext2222name
1111111ex222name

I have a column of phone numbers that looks like this. The phone number could be followed by an extension then followed by a name. I'd like to remove the name from the phone number. How can I make it?

CodePudding user response：

With just a bit of string manipulation

Declare @YourTable Table ([PhoneNumber] varchar(50))
Insert Into @YourTable Values 
 ('1111111')
,('1111111name')
,('1111111ext2222name')
,('1111111ex222name')
 
Select * 
      ,NewValue = substring([PhoneNumber],1,len([PhoneNumber])-patindex('%[0-9]%',reverse([PhoneNumber])))
 From  @YourTable

Results

PhoneNumber         NewValue
1111111             111111
1111111name         111111
1111111ext2222name  1111111ext222
1111111ex222name    1111111ex22