SQL Server: Remove Numbers from String

1 year ago
Ava Mitchell
2 minutes

In SQL Server, the REPLACE function can be used to replace numbers in a string.

Here is an example:

DECLARE @str VARCHAR(50) = 'abc123xyz456'
DECLARE @newStr VARCHAR(50)

SELECT @newStr = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str,
    '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')

SELECT @newStr

Running the example code above will replace the numbers in the string “abc123xyz456” with an empty string, resulting in “abcxyz”.

In the REPLACE function, we used multiple instances of the REPLACE function consecutively to replace each digit in the string. Initially, we replaced the digit 0 with an empty string, then replaced 1 with an empty string, and so on up to 9. This way, the final result is a string without any digits.

It is important to note that the above examples can only replace numeric characters in the string. If the string contains any characters other than numeric characters, they will not be replaced. If you need to replace other characters in the string, you can specify the characters to be replaced in the first parameter of the REPLACE function.

bannerAds