Replace non-numeric characters in a column by a number

Question

Hello,

I have a column(varchar) in my table that contains numbers but also strings. Is it possible to replace all non-numeric characters in this column by numbers? How to do that?

Answer 1

Hi @Kabdar

If I guess right, you might try this solution using TRANSLATE function which is available on SQL Server 2017 (14.x) and later and also Azure SQL.

Syntax: TRANSLATE (inputString,characters, translations)

Here is a sample replaces any non-numeric characters with number 0:

DECLARE @Temp_Table TABLE(Col VARCHAR(20))
INSERT INTO @Temp_Table VALUES
('Ajs57uj9T'),('PH89Ths67'),('URT889hsy')
SELECT TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '0000000000000000000000000000000000') AS New_Col
FROM @Temp_Table

Note that the character number of the two arguments characters and translations should be same. In the sample above, they both have 34 characters.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

Its better to send here perfect example, anyways you may try replace string function from below article:

https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver16

For example I have a varchar column in a table, I have used

replace('nam 2002681','nam','123') to replace 'nam' with '123' in the string.

Regards,