How to remove data from a column where there is no number

Question

How to remove data from a column where there is no number

sujith kumar matharasi 351

Hi All,

I have a column which has sample values such as

Column1
abc
abc1
123

I would like to have the output as :

column1

abc1
123

I would like to replace blank for where there is only text and no number in the column.

Accepted answer

1 additional answer

Your answer

Answer 1

Stefan Hoffmann 621

Use LIKE or PATINDEX with an number detection pattern:

DECLARE @Table TABLE (
    Column1 NVARCHAR(255)
);

INSERT INTO @Table ( Column1 )
VALUES ( N'abc' ) ,
       ( N'abc1' ) ,
       ( N'123' );

SELECT *
FROM   @Table T
WHERE  PATINDEX('%[0-9]%', T.Column1) > 0;

SELECT *
FROM   @Table T
WHERE  T.Column1 LIKE '%[0-9]%';

DELETE @Table
WHERE NOT Column1 LIKE '%[0-9]%';

SELECT *
FROM   @Table T;

Then your desired result is found by negating it.

sujith kumar matharasi 351 Reputation points

2020-10-13T16:58:00.583+00:00

It looks like this code is removing the row where there is text however i want that to be replaced by a blank and not to be removed entirely.
sujith kumar matharasi 351 Reputation points

2020-10-13T17:27:40.283+00:00

I modified the code to below and it worked for my requirement. Thanks

SELECT *, CASE WHEN PATINDEX('%[0-9]%', T.Column1) > 0 THEN T.Column1 ELSE '' END AS NewCOlumn
FROM @Table T

Answer 2

Yitzhak Khabinsky 26,586

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Col NVARCHAR(255));
INSERT INTO @tbl (Col) VALUES 
(N'abc'),
(N'abc1'),
(N'123');
-- DDL and sample data population, end

SELECT ID, Col AS [Before]
    , IIF(PATINDEX('%[0-9]%', Col) > 0, col,'') AS [After]
FROM @tbl;

Share via

How to remove data from a column where there is no number

1 additional answer

Your answer