insert substring into new column

问题:

I have a db where one column contains 2 pieces of data, e.g. first and last name.

The format is roughly ABC-1D23-4F34

I want to copy and insert the first 3 letters, the ABC, into a new column. Lets call these columns [full_id] and [ref_id]

From reading it looks like substring is able to do this but I am doing something wrong here.

INSERT INTO [ref_id]

SUBSTRING([full_id], 1, 3)

FROM db.Name

Thank you for the help.

---

EDIT:

The update string worked. But I found that there are issues with my data and it is not all in proper formatting.

Is there a way to write a case where if the substring is not 3 letters it writes a null value?

Thanks again, and sorry for having bad data.

回答1:

Try

UPDATE Name
SET ref_id = CASE WHEN CHARINDEX('-',full_id) = 4 THEN SUBSTRING(full_id,1,3) ELSE NULL END

That will set the ref_id column for all rows using the first 3 characters of the full_id column.

回答2:

If it is a column in the same table you need to switch to an update statement.

UPDATE db.Name SET [ref_id] = SUBSTRING([full_id], 1, 3)

回答3:

Perhaps you want something like:

Update db.Name set ref_id = substring([full_id], 1,3)