0

I need help in updating records in a table. Currently, I have a % value which I would like to remove from all the records. I have over 4000 records in this table.

Date       Server   CPU   MEM   DRVC
------------------------------------
01/10/10   DRV01    90%   80%   40%

I want to have the data values remain unchanged but "%" sign to be removed.

Date       Server   CPU   MEM   DRVC
------------------------------------
01/10/10   DRV01     90    80    40

In my case I need to update three column records

CPU
MEM
DRVC

declare @x = CPU
declare @y = MEM
declare @z = DRVC
SELECT REPLACE(@x, '%', '') from table01;
SELECT REPLACE(@y, '%', '') from table01;
SELECT REPLACE(@z, '%', '') from table01;
Ilyes
  • 14,640
  • 4
  • 29
  • 55
StillLearning
  • 15
  • 1
  • 4
  • Have you written the `UPDATE` statement? This post should give you the answer: https://stackoverflow.com/questions/18693349/how-do-i-find-with-the-like-operator-in-sql-server – Jacob H May 02 '19 at 19:17
  • 5
    Now would be a good time to change those from varchar to numeric too... – JNevill May 02 '19 at 19:28

3 Answers3

4

You can use REPLACE() function to remove it as

SELECT [Date],
       [Server],
       REPLACE([CPU], '%', '') [CPU],
       REPLACE([MEM], '%', '') [MEM],
       REPLACE([DRVC], '%', '') [DRVC]
FROM [table01];

The REPLACE() function replaces all occurrences of a specified string value ('%' in your case) with another string value.

The query will returns:

Date       Server   CPU   MEM   DRVC
------------------------------------
01/10/10   DRV01    90    80    40

If you really want to UPDATE your table as you mention in the title "Would like to update a SQL Server table data by removing the special character “%” from all the records" then

UPDATE [table01]
SET [CPU] = REPLACE([CPU], '%', ''),
    [MEM] = REPLACE([MEM], '%', ''),
    [DRVC] = REPLACE([DRVC], '%', '')
--WHERE <Type your conditions here>

Finally, you need to choose the right datatype for your columns, since CPU, MEM and DRVC are numeric values, then try to use a numeric datatype which is the proper way, you can use INT, BIGINT, DECIMAL... according to your requirement. See Data types and ALTER TABLE.

Ilyes
  • 14,640
  • 4
  • 29
  • 55
2

Just use the REPLACE function...

DECLARE @x VARCHAR(100) = 'hello%world';
SELECT REPLACE(@x, '%', '')

Edit... adding code to explicitly show how to perform an actual UPDATE... 

  -- create some test data...
    IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
    BEGIN DROP TABLE #TestData; END;

    CREATE TABLE #TestData (
        [Date] DATE NOT NULL,
        [Server] VARCHAR(50) NOT NULL,
        CPU VARCHAR(4) NOT NULL,
        MEM VARCHAR(4) NOT NULL,
        DRVC VARCHAR(4) NOT NULL 
        );
    INSERT #TestData (Date, Server, CPU, MEM, DRVC) VALUES
        (GETDATE(), 'DRV01', '90%', '80%', '40%');

    -- check the initial state of the test data.
    SELECT * FROM #TestData td;

    -- UPDATE the data to remove the % symbols.
    UPDATE td SET 
        td.CPU = REPLACE(td.CPU, '%', ''),
        td.MEM = REPLACE(td.MEM, '%', ''),
        td.DRVC = REPLACE(td.DRVC, '%', '')
    FROM
        #TestData td;

    -- check the post-update state of the test data.
    SELECT * FROM #TestData td;
Jason A. Long
  • 4,382
  • 1
  • 12
  • 17
  • sorry, guys, I am a beginner kindly correct me if I am making a mistake. In my case I need to update three column records CPU MEM DRVC declare @x = CPU declare @y = MEM declare @z = DRVC SELECT REPLACE(@x, '%', '') from table01; SELECT REPLACE(@y, '%', '') from table01; SELECT REPLACE(@z, '%', '') from table01; – StillLearning May 02 '19 at 19:45
  • @StillLearning - I've updated my original answer to better illustrate how to write this into an actual UPDATE command. – Jason A. Long May 03 '19 at 13:59
0

REPLACE() function:

select replace('40%','%','')
Edvaldo Lucena
  • 15
  • 1
  • 7