CODEWITHSUNDEEP
sqloracle-databaseoracle11gsubstring
user3266259
user3266259

Reputation: 399

Using substring in SQL update statement

I'm writing a SQL query that updates the CUST_NAME column by appending the the word "CHECKED to it. The column is only 100 characters long however and I'm going to run into errors if I have an already long customer name and try appending CHECKED to it. So I want to use the Substring function in sql, but kind of stumped. I want to be able to substring the CUST_NAME field if it will go over 100 characters with the appended word. How can I do that? Thank you

UPDATE CUST_INFO cust
  SET CUST_NAME = (CUST_NAME||'_CHECKED')
  WHERE process_timestamp = null;

Upvotes: 3

Views: 19112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 4

Related Questions