CODEWITHSUNDEEP
sqlstringoracle-databaseoracle11gsql-update
MaksIngenr
MaksIngenr

Reputation: 71

update the first character in the column, in queries oracle

I have an "Invoices" table. The table is 190 thousand rows. But there is a problem. In the column "Docnumber" all records of this format "Y-1000", "Y-1001" and I want to bond in all records from Y to P. That there were all records of this format "P-1000", "P-1001". But I do not understand how to do it right, I will be grateful for the help!

Upvotes: 0

Views: 1624

Answers (3)

Arijit Kanrar
Arijit Kanrar

Reputation: 450

This is a simple replace of Y to P in the column. So a simple update with the REPLACE function will solve the problem.

UPDATE invoices
SET docnumber = REPLACE (docnumber, 'Y', 'P');

Of course this assumes that the rest of the string cannot contain a P. If it can, then a regular expression based replace will work using the REGEXP_REPLACEfunction which will only replace the beginning Y.

UPDATE invoices
SET docnumber = REGEXP_REPLACE (docnumber, '^Y', 'P');

The following query shows that REGEXP_REPLACE only replaces the beginning Y

WITH
    inv
    AS
        (SELECT 'Y-8817Y' AS docnumber FROM DUAL
         UNION
         SELECT 'Y-8884' AS docnumber FROM DUAL)
SELECT docnumber,
       REGEXP_REPLACE (docnumber, '^Y', 'P') AS replaced_y
FROM inv;

This is the result:

DOCNUMBER   REPLACED_Y
Y-8817Y     P-8817Y
Y-8884      P-8884

Upvotes: 1

Atif
Atif

Reputation: 2210

Please use below query:

update Invoice_test set docnumber = 'P'||SUBSTR(DOCNUMBER, 2) where SUBSTR(DOCNUMBER, 1,1) = 'Y';

Upvotes: 0

GMB
GMB

Reputation: 222432

You can use string functions. The following replaces the first character of every value in the column with 'P':

update mytable t set docnumber = 'P' || substr(docnumber, 2)

Depending on your actual requirement, you can be a little more specific, for example by ensuring that docnumber starts with 'Y':

update mytable t set docnumber = 'P' || substr(docnumber, 2) 
where docnumber like 'Y%'

Upvotes: 4

Related Questions