CODEWITHSUNDEEP
oracle-databaseplsql
Ritu Jha
Ritu Jha

Reputation: 1

the update query is not working in oracle sql developer , what is the error?

DECLARE
   @A varchar2(20);
BEGIN
     @A := 'abc555';

UPDATE TEST Set LINK = Replace(LINK,Substring(LINK,1,CHARINDEX('\ESSAIS-CGMP',LINK)-1),@A);

END;

Upvotes: 0

Views: 787

Answers (1)

MT0
MT0

Reputation: 168570

You are using Oracle not SQL server.

SUBSTRING and CHARINDEX are not valid Oracle functions; you want SUBSTR and INSTR instead.

And, @ is not a valid first character for a non-quoted identifier, just use A rather than @A.

DECLARE
  A varchar2(20);
BEGIN
  A := 'abc555';

  UPDATE TEST
  Set LINK = Replace(LINK,Substr(LINK,1,INSTR(link,'\ESSAIS-CGMP')-1),A);
END;
/

Then, for the sample data:

CREATE TABLE test ( link VARCHAR2(50) );

INSERT INTO test ( link ) VALUES ( 'here\ESSAIS-CGMP' );

The result is:

SELECT * FROM test;

| LINK               |
| :----------------- |
| abc555\ESSAIS-CGMP |

db<>fiddle here

Upvotes: 2

Related Questions