user3266259 user3266259 - 2 months ago 11
SQL Question

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;

Answer

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.