CODEWITHSUNDEEP
sqlsql-server
Doug Niman
Doug Niman

Reputation: 3

Update column - remove first and last characters

I have written a successful SELECT statement to both show me all the company names that are surrounded by double quotes like "ABC Company" for example. I want to permanently remove the double quotes and replace the company name with the cleaned version.

Here is my SELECT that works great:

SELECT
    companyname,
    CASE
       WHEN companyname LIKE '"%"' 
          THEN SUBSTRING(companyname, 2, LEN(companyname) - 2)
       ELSE companyname
    END AS CC
FROM 
    Company_Table 
WHERE 
    companyName LIKE '"%"'

I am having trouble converting this statement to an UPDATE. I want to affect the original companyname column in place.

I have tried to add UPDATE clauses around the above code, but I just cannot seem to get it right.

Can I please get an example of how I can remove the first and last chars of a column in place.

Otherwise, it is manual labor!

I expect that the original companyname field will be devoid of "".

Upvotes: 0

Views: 2524

Answers (3)

shawnt00
shawnt00

Reputation: 17915

If there's any chance of a malformed input you need to avoid a negative length argument:

CASE WHEN LEN(companyname) > 2
     THEN SUBSTRING(companyname, 2, LEN(companyname) - 2)
     ELSE '' END;

Upvotes: 0

Isaac
Isaac

Reputation: 3363

If you are sure you don't have double quotes anywhere but the beginning and end of your companyName values you could use the REPLACE function to replace them with an empty string. 

UPDATE Company_Table 
SET companyname = REPLACE(companyname, '"', '')
WHERE companyName like '"%"'

Note that the second parameter is a double quote surrounded by single quotes.

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74595

As an update:

UPDATE Company_Table 
SET companyname = SUBSTRING(companyname, 2, LEN(companyname)-2)
WHERE companyName like '"%"'

Single table update queries take the form

UPDATE table
SET column1 = value1, column2 = value2 ...
WHERE truth_test

You can use the same column names on the left and the right of the = in the SET. All the right hand sides are worked out then applied to the lefts so the values don't interfere with each other

(if you were to say SET a = a + 1, b = a + 1 then for a row where both a and b started out as 0, they would both be 1 after the update. The a doesn't increment from 0 to 1 then the b become 1+1. In other words, b is set to old-a + 1 not new-a + 1

Upvotes: 4

Related Questions