remove values from string

Shambhu Rai 1,406

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

Yitzhak Khabinsky 25,721 Reputation points

2022-08-17T12:46:42.307+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Anurag Sharma 17,586 Reputation points

2022-08-22T05:43:55.353+00:00

@Shambhu Rai thanks for accepting answer. Please let us know if you have any other questions.

Accepted answer

Yitzhak Khabinsky 25,721

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 |  
+----+---------------+----------------+-----------------+

5 additional answers

Ebrahim Bazarjani 5

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

Share via

remove values from string

5 additional answers