remove values from string

Question

Hi Expert,

how will i remove all the characters from values in sql

Create table test1
(id string, details string)

insert into test1
values('20kjk','3ddf')

Regards

Answer 1

Hi @Shambhu Rai ,

Please try the following solution. It will work starting from SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, details VARCHAR(100));  
INSERT INTO @tbl values   
('20kjk'),  
('3ddf'),  
('561.456/.7890');  
-- DDL and sample data population, end  
  
-- SQL Server 2017 onwards  
;WITH rs AS   
(  
   SELECT *  
      , REPLACE(TRANSLATE(details, '0123456789', SPACE(10)),' ','') AS JunkCharacters  
   FROM @tbl  
)  
SELECT rs.*  
   , REPLACE(TRANSLATE(details, TRIM(JunkCharacters), SPACE(LEN(TRIM(JunkCharacters)))),' ','') AS CleansedDetails  
FROM rs;  

Output

+----+---------------+----------------+-----------------+  
| ID |    details    | JunkCharacters | CleansedDetails |  
+----+---------------+----------------+-----------------+  
|  1 | 20kjk         | kjk            |              20 |  
|  2 | 3ddf          | ddf            |               3 |  
|  3 | 561.456/.7890 | ./.            |      5614567890 |  
+----+---------------+----------------+-----------------+  

Answer 2

Hi @Shambhu Rai , welcome to Microsoft Q&A platform.

If the number always comes at the start, then you can use below:

select LEFT('3ddf', PATINDEX('%[0-9][^0-9]%', '3ddf'))  

You can also use the same in insert statement before inserting it to database:

insert into test1  
    values(LEFT('20kjk', PATINDEX('%[0-9][^0-9]%', '20kjk' )) ,LEFT('3ddf', PATINDEX('%[0-9][^0-9]%', '3ddf' )))  

Please let me know if this helps or else we can discuss further.

Reference: split alpha and numeric using sql

Answer 3

Create this function:

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))  
RETURNS VARCHAR(1000)  
AS  
BEGIN  
    WHILE PATINDEX('%[^0-9]%', @strText) > 0  
    BEGIN  
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')  
    END  
    RETURN @strText  
END  

then you can use it as shown below:

CREATE TABLE test1  
(id varchar(100), details varchar(100))  
  
INSERT INTO test1  
([Id], [details])  
SELECT dbo.fnRemoveNonNumericCharacters('20kjk'), dbo.fnRemoveNonNumericCharacters('3ddf')  

Answer 4

Hi

Please check a look at this thread，hope will help you.
https://sqlzealots.com/2017/05/28/how-to-extract-only-numbers-from-a-string-in-sql-server/

Best Regards，
Isabella

Answer 5

Hi @Shambhu Rai
Have you tried the TRANSLATE function as Yitzhak answered? If your SQL Sever doesn't support this function, then you could have a try on looped REPLACE.
Check this:

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, details VARCHAR(100));  
INSERT INTO @tbl values   
 ('20kjk'),  
 ('3ddf'),  
 ('ER78Y9'),  
 ('561.456/.7890');  
      
SELECT *,   
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(  
	   details,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')AS New_Value  
FROM @tbl  

Best regards,
LiHong