-1

I have values in my column as below. Can anyone help me with how to replace any numeric data present in a column or string to blank using SQL Server query?

enter image description here

Below is the column data. How do I replace the numbers to blank and display only underscores.

Dale K
  • 25,246
  • 15
  • 42
  • 71
deepak revanaki
  • 19
  • 1
  • 6
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K May 25 '21 at 07:08
  • use [translate()](https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver15) – Squirrel May 25 '21 at 07:14
  • to separate numeric part and do any operation on it, check this link it might help : https://stackoverflow.com/q/10443462/6876710 – El.Hum May 25 '21 at 07:16

2 Answers2

1

You could approach this by counting the number of underscores, and then generating a string containing this number of underscores:

SELECT Column1, REPLICATE('_', LEN(Column1) - LEN(REPLACE(Column1, '_', '')))
FROM yourTable;

screen capture from demo link below

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

Here is a more generic solution. It will handle not just the underscore chars. It will work starting from SQL Server 2017 onwards.

As @Squirrel correctly mentioned, the TRANSLATE() function is very handy for such cases.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col VARCHAR(256));
INSERT INTO @tbl (col) VALUES
('2413347_6752318'),
('7263_872_767'),
('123Code456');
-- DDL and sample data population, end

SELECT col AS [Before] 
    , REPLACE(TRANSLATE(col, '0123456789', SPACE(10)), SPACE(1), '') AS [After]
FROM @tbl;

Output

+-----------------+-------+
|     Before      | After |
+-----------------+-------+
| 2413347_6752318 | _     |
| 7263_872_767    | __    |
| 123Code456      | Code  |
+-----------------+-------+
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21