How to Replace Specific Values in a Oracle Database Column

How to replace specific values in a oracle database column?

Use REPLACE:

SELECT REPLACE(t.column, 'est1', 'rest1')
  FROM MY_TABLE t

If you want to update the values in the table, use:

UPDATE MY_TABLE t
   SET column = REPLACE(t.column, 'est1', 'rest1')

How to replace specific word on a large text in a column using Oracle

Your statement:

update table set desc = REPLACE('words = 20', 'words', 'Alpha') where ...;

is replacing 'words' in the fixed string 'words = 20`; it isn't acting on the existing column value you at all.

You could do:

update table set desc = REPLACE(desc, 'words = 20', 'Alpha = 20') where ...;

db<>fiddle (using valid table and column names).

If you want to replace all occurrences of 'words', not just 'words = 20', then remove the = 20 from both arguments; but from your attempt I think you want it to be more targetted.

How to replace a value in a comma separated string column in Oracle

In Oracle, you can use simple (quick) string functions:

SELECT id,
       TRIM(BOTH ',' FROM REPLACE(','||lobs||',', ',4,', ',2,'))
         AS updated_lobs
FROM   table_name;

Or (slower) regular expressions:

SELECT id,
       REGEXP_REPLACE(lobs,'(^|,)4(,|$)','\12\2') AS updated_lobs
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (ID, LOBS) AS
SELECT 1, '1,4,64,7,8' FROM DUAL UNION ALL
SELECT 2, '4,1,5,64,7,9' FROM DUAL UNION ALL
SELECT 3, '3,5,64,8,11,4' FROM DUAL;

Both output:























IDUPDATED_LOBS
11,2,64,7,8
22,1,5,64,7,9
33,5,64,8,11,2

How to replace part of string in a column, in oracle

REPLACE doesn't use wildcards, it simply replaces all instances of the first string with the second string. This should work:

UPDATE Mytable t
   SET column = REPLACE(t.U_MSG, ', CALL HELPDESK', ' CALL HELPDESK')

Update column values by removing values after decimal points and comma in oracle

You should be able to use the ROUND function for that.

select ROUND(CAST(REPLACE('6983.80', ',') as number)), ROUND(1.4), ROUND(1.5), ROUND(1.6) from dual;

yields 1,2,2.

In your case, as your input is NVARCHAR2, it would be

update table1 set col1 = CAST(ROUND(CAST(REPLACE(col1, ',') as NUMBER)) as NVARCHAR2(6));

Please save numbers as a number type and not as string to avoid complicated castings

Documentation is here

How to replace a character from a String in SQL?

Are you sure that the data stored in the database is actually a question mark? I would tend to suspect from the sample data that the problem is one of character set conversion where ? is being used as the replacement character when the character can't be represented in the client character set. Possibly, the database is actually storing Microsoft "smart quote" characters rather than simple apostrophes.

What does the DUMP function show is actually stored in the database?

SELECT column_name,
       dump(column_name,1016)
  FROM your_table
 WHERE <<predicate that returns just the sample data you posted>>

What application are you using to view the data? What is the client's NLS_LANG set to?

What is the database and national character set? Is the data stored in a VARCHAR2 column? Or NVARCHAR2?

SELECT parameter, value
  FROM v$nls_parameters
 WHERE parameter LIKE '%CHARACTERSET';

If all the problem characters are stored in the database as 0x19 (decimal 25), your REPLACE would need to be something like

UPDATE table_name
   SET column1 = REPLACE(column1, chr(25), q'[']'),
       column2 = REPLACE(column2, chr(25), q'[']'),
       ...
       columnN = REPLACE(columnN, chr(25), q'[']')
 WHERE INSTR(column1,chr(25)) > 0
    OR INSTR(column2,chr(25)) > 0 
    ...
    OR INSTR(columnN,chr(25)) > 0

Performing multiple Replace operations on an Oracle database column

You can nest the replace() calls:

UPDATE TABLE1
    SET COLUMN1 = REPLACE(REPLACE(REPLACE(REPLACE(COLUMN1, 'ABC', 'SR1'), '123', 'SR2'), 'XYZ', 'SR3'), '789', 'SR4');

Oracle also offers regexp_replace(). You might find this handy for some of your data transformations.

Replace values in column with Oracle

UPDATE TABLE tablename
SET salary = 2250
WHERE salary = 2352

I'm not sure what you're aiming for with the REPLACE() function but if you want to change the values then you need to do it like the above code. Set the salary to what you want WHERE it has a salary of 2250.

You can write it a few times with the different criteria and then run it.

EDIT: Since you're worried about doing this numerous times you can create a table called salaries:

CREATE TABLE t_salary AS 
SELECT salary from tablename;

ALTER t_salary add newsalary integer after salary;

In the 'newsalary' column you can add what the new salary should be then do an inner join. I just created a table for this purpose called stackoverflow (which would be your 'tablename'

update stackoverflow s
inner join t_salary ns on s.salary = ns.salary
set s.salary = ns.newsalary;

Now what this will do is join tablename to t_salary where the current salary = the salary in t_salary. Then you set the tablename.salary equal to the new salary, this worked for me I hope it works for you.

Note, the syntax may be slightly different since I don't have Oracle installed on my home machine, I used MySQL.

How to ADD specific values in a oracle database column?

You could use regexp_replace() to insert 'qwe' just before the final digit(s), if any:

select 
    id, 
    name, 
    case when regexp_like(value, '\d+$')
        then regexp_replace(value, '^(\w+)(\d+)$', '\1' || 'qwe' || '\2')
        else value
    end value
from emp

If value ends with digits, regexp_replace() inserts 'qwe' before the digits. If not, value is left untouched.

If you want an update:

update emp
set value = regexp_replace(value, '^(\w+)(\d+)$', '\1' || 'qwe' || '\2')
where regexp_like(value, '\d+$')

Demo on DB Fiddle:


ID | NAME | VALUE  
-: | :--- | :------
 1 | a    | hel    
 2 | b    | devqwe2
 3 | c    | newqwe3
 4 | d    | canqwe6
 5 | e    | kunqwe8
 6 | f    | luvqwe4

Related Topics

MySQL 'Create Schema' and 'Create Database' - Is There Any Difference

Primary Key/Foreign Key Naming Convention

SQL Script to Alter All Foreign Keys to Add on Delete Cascade

How Do Composite Indexes Work

How to Create a New Database with the Hstore Extension Already Installed

SQL Query to Group by Day

To Ignore Duplicate Keys During 'Copy From' in Postgresql

How to Select Exists Directly as a Bit

Restrict Varchar() Column to Specific Values

What Does Collation Mean

Use Variable with Top in Select Statement in SQL Server Without Making It Dynamic

How to Replace Specific Values in a Oracle Database Column

How to Find the Data Directory for a SQL Server Instance

Postgresql Query Between Date Ranges

Backup a Single Table with Its Data from a Database in SQL Server 2008

Get Everything After and Before Certain Character in SQL Server

Update Multiple Rows with One Query

Division (/) Not Giving My Answer in Postgresql


Leave a reply



Submit