Update Substring of a Column

Update substring of a column

UPDATE Meter
SET Name = 'ZAA_' + SUBSTRING(Name, 4, LEN(Name))
WHERE SUBSTRING(Name, 1, 4) = 'ZAA\'

Edit:

Or as @Damien_The_Unbliever states, to use an index:

UPDATE Meter
SET Name = 'ZAA_' + SUBSTRING(Name, 4, LEN(Name))
WHERE Name LIKE 'ZAA\%'

EDIT

From your comment, try this statement to fix the additional \:

UPDATE Meter
SET Name = 'ZAA_' + SUBSTRING(Name, 5, LEN(Name))
WHERE Name LIKE 'ZAA_\%'

UPDATE and REPLACE part of a string

You don't need wildcards in the REPLACE - it just finds the string you enter for the second argument, so the following should work:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

If the column to replace is type text or ntext you need to cast it to nvarchar

UPDATE dbo.xxx
SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '')
WHERE ID <=4

SQL update part of string

Replace it

update blist
set serial_no = replace(serial_no, 'abcd_', 'xyz_')
where city = 'US'
  and serial_no like 'abcd%'

Demo

How I can use Substring for Update in SQL right?

I don't know if below query would help, because as I mentioned in the comment you should provide data examples and expected results.

The correct UPDATE syntax is:

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference [PARTITION
(partition_list)] [FOR PORTION OF period FROM expr1 TO expr2] SET
col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ... [WHERE
where_condition] [ORDER BY ...] [LIMIT row_count] Multiple-table
syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
[WHERE where_condition]

So your update query should be:

update data d
inner join history h on d.contract = h.contract 
set  d.user = v.user, d.upload= h.`timestamp` 
where d.filename in (SELECT DISTINCT SUBSTRING(h.action,LOCATE('"',h.action)+1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action)))) 
and h.action like 'File%added' ;

How can I use multiple substring functions in an update statement?

You must concatenate the 2 parts of the name:

UPDATE candidates
SET candidate_name = CONCAT( 
  SUBSTRING(candidate_name, LOCATE(',', candidate_name) + 2, LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1),
  ' ',
  SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1)
)
WHERE candidate_name = 'AHREND, JARED';

See the demo.

or, simpler with SUBSTRING_INDEX():

UPDATE candidates
SET candidate_name = CONCAT( 
  TRIM(SUBSTRING_INDEX(candidate_name, ',', -1)),
  ' ',
  SUBSTRING_INDEX(candidate_name, ',', 1)
)
WHERE candidate_name = 'AHREND, JARED';

See the demo.

How to update column of each row with a substring value of another column?

You don't need a subquery. All you need is this:

set registry = cast(SUBSTR(split_part(data_sent, 
'registry>', 2), 1, 26) as numeric

How to use SUBSTR in update query

TIMESTAMP - TIMESTAMP gives an INTERVAL DAY TO SECOND result (not a string) - just add that to an epoch data and use TO_CHAR to get the time component:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE EMPLOYEE_LOGIN_TIME(
  EMPLOYEE_ID         INTEGER,
  TIME_IN             TIMESTAMP(6) WITH LOCAL TIME ZONE,
  TIME_OUT            TIMESTAMP(6) WITH LOCAL TIME ZONE,
  WORKING_HOUR        VARCHAR2(30 BYTE),
  PUNCH_DATE          DATE,
  TIME_OUT_HISTORY    VARCHAR2(200 BYTE)
);

INSERT INTO EMPLOYEE_LOGIN_TIME ( 
  EMPLOYEE_ID,
  TIME_IN,
  TIME_OUT,
  WORKING_HOUR,
  PUNCH_DATE,
  TIME_OUT_HISTORY
)
SELECT 73584,
       SYSTIMESTAMP,
       SYSTIMESTAMP + INTERVAL '+000000000 01:14:00.771000000' DAY TO SECOND,
       NULL,
       SYSDATE,
       NULL
FROM   DUAL;

Query 1:

UPDATE  EMPLOYEE_LOGIN_TIME
SET WORKING_HOUR = TO_CHAR( 
                     DATE '0001-01-01' + ( TIME_OUT- TIME_IN ),
                     'HH24:MI:SS'
                   )
WHERE  EMPLOYEE_ID= 73584
AND    PUNCH_DATE >= TRUNC(SYSDATE)
AND    PUNCH_DATE <  TRUNC(SYSDATE) + INTERVAL '1' DAY;

Query 2:

SELECT EMPLOYEE_ID,
       TO_CHAR( TIME_IN,  'YYYY-MM-DD HH24:MI:SS.FF6 TZR' ) AS TIME_IN,
       TO_CHAR( TIME_OUT, 'YYYY-MM-DD HH24:MI:SS.FF6 TZR' ) AS TIME_OUT,
       WORKING_HOUR,
       PUNCH_DATE,
       TIME_OUT_HISTORY
FROM   EMPLOYEE_LOGIN_TIME;

Results:

| EMPLOYEE_ID |                        TIME_IN |                       TIME_OUT | WORKING_HOUR |          PUNCH_DATE | TIME_OUT_HISTORY |
|-------------|--------------------------------|--------------------------------|--------------|---------------------|------------------|
|       73584 | 2018-05-14 09:00:00.000000 PST | 2018-05-14 10:14:00.771000 PST |     01:14:00 | 2018-05-14 09:00:00 |           (null) |

MYSQL: Update a column value from a substring in another column?

Use SUBSTRING_INDEX:

UPDATE yourTable
SET link_Id = SUBSTRING_INDEX(Applicant, '_', -1);

Using substring in SQL update statement

Here is one way:

UPDATE CUST_INFO cust
    SET CUST_NAME = SUBSTR(CUST_NAME, 1, 92) || '_CHECKED'
    WHERE process_timestamp is null;

Also, if you want to update any records, then use is null rather than = null (the latter never evaluates to true).

Note: Not all databases have the left() function, you can use substr() or the equivalent instead.

---

Related Topics