CODEWITHSUNDEEP
sqlsql-server-2008
Snake Eyes
Snake Eyes

Reputation: 16754

Update every row with a value from another column from same table

I have the following example of table content

+ Column1 | Column2 | Column3 +
+---------|---------|---------+
+   1     | val.txt |         +
+   2     | test.xls|         +
+   3     | abc.dwg |         +
+   4     | y.txt   |         +
+---------|---------|---------+

I want to update Column3 with information from Column2, which means I want to (in this case) extract the extension from Column2 and put it in Column3.

I want the result to look like

+ Column1 | Column2 | Column3 +
+---------|---------|---------+
+   1     | val.txt |   .txt  +
+   2     | test.xls|   .xls  +
+   3     | abc.dwg |   .dwg  +
+   4     | y.txt   |   .txt  +
+---------|---------|---------+

How to do that with an UPDATE statement?

I know how to extract the extension:

SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2)) AS Extension

Upvotes: 11

Views: 27250

Answers (3)

Martin Smith
Martin Smith

Reputation: 452977

To extract the last 4 characters the simplest way is 

UPDATE dbo.YourTable
SET Column3 = RIGHT(Column2,4);

To extract everything to the right of the last dot and so work correctly with file names such as Foo.designer.cs you can use

UPDATE dbo.YourTable
SET    Column3 = '.' + RIGHT(Column2, CHARINDEX('.', REVERSE('.' + Column2)) - 1)

Upvotes: 0

Buzz
Buzz

Reputation: 6320

try this 

 UPDATE dbo.YourTable
    SET Column3 =SUBSTRING(Column2,CHARINDEX('.',Column2,0),(LEN(Column2)-CHARINDEX('.',Column2,0)+1))

Upvotes: 1

marc_s
marc_s

Reputation: 754240

How about this:

UPDATE dbo.YourTable
SET Column3 = SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2))

If needed, you can also include a WHERE clause to limit the rows being updated, e.g.:

UPDATE dbo.YourTable
SET Column3 = SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2)) 
WHERE Column3 IS NULL

or something like that.

Upvotes: 21

Related Questions