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,

You can Use This :

CREATE FUNCTION udfFG_GetNumFromStr (@Str VARCHAR(64))
RETURNS VARCHAR(64)
AS
BEGIN
	DECLARE @Pos int,@Char char(1)
	SET @Pos = PATINDEX('%[^0-9]%',@Str)
	WHILE @Pos > 0
	BEGIN
		SET @Char = SUBSTRING(@Str,@Pos,1)
		SET @Str = REPLACE(@Str,@Char,'')
		SET @Pos = PATINDEX('%[^0-9]%',@Str)
	END
RETURN @Str
END