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?
Below is the column data. How do I replace the numbers to blank and display only underscores.
-
6As 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– Dale K2021-05-25 07:08:13 +00:00Commented May 25, 2021 at 7:08
-
use translate()Squirrel– Squirrel2021-05-25 07:14:37 +00:00Commented May 25, 2021 at 7:14
-
to separate numeric part and do any operation on it, check this link it might help : stackoverflow.com/q/10443462/6876710El.Hum– El.Hum2021-05-25 07:16:35 +00:00Commented May 25, 2021 at 7:16
Add a comment
|
2 Answers
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;
Demo
Comments
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 |
+-----------------+-------+
