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?
UPDATE CUST_INFO cust
SET CUST_NAME = (CUST_NAME||'_CHECKED')
WHERE process_timestamp = null;
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.