set column value to part of a string in another column
i have a table with a column named CustomerName which stores a customer's full name(thats both开发者_高级运维 names of a customer - first name, last name and any other names).
I want to redesign this table such that instead of just having CustomerName field, i should have CustomerFirstName, CustomerLastName and CustomerOtherNames(then customerName can be a concatenation of the 3 fields).
i already have customer records in the table with CustomerNames in format below
CustomerName
Tom John
Mary Joy
San Roy
now i need to run an update query that will set Tom, Mary and San as CustomerFirstName and set John, Joy and Roy as CustomerLastName for their respective rows but am stuck on this.
The following integrates the formulas suggested by Matt (Lima) into the actual UPDATE query; it also deals with various cases such as
- when there is only a FirstName,
- when there are leading or trailing spaces
- when there's more than one space separating first name from last name
Alternatively, one may do away with the extra tests aimed at ensuring that there is a space, by adding a WHERE clause (WHERE CustomerName like ('% %').
-- Ensure no leading nor trailing spaces
UPDATE myTable
SET CustomerName = TRIM(CustomerName)
UPDATE myTable
SET FirstName = TRIM(LEFT(TRIM(CustomerName),
CASE CHARINDEX(' ', CustomerName)
WHEN 0 THEN LEN(CustomerName)
ELSE CHARINDEX(' ', CustomerName) -1
END)),
LastName = CASE CHARINDEX(' ', CustomerName)
WHEN 0 THEN ''
ELSE TRIM(SUBSTRING(CustomerName, CHARINDEX(' ', CustomerName) + 1, LEN(CustomerName))
END
You might be able to use something like:
SELECT SUBSTRING(CustomerName, 1, CHARINDEX(' ', CustomerName) - 1) AS [FirstName],
SUBSTRING(CustomerName, CHARINDEX(' ', CustomerName) + 1, LEN(CustomerName)) AS [LastName]
FROM yourTableName
I got this solution from: http://www.sql-server-helper.com/tips/split-name.aspx
Hope this helps.
Matt
Try something like: SELECT SUBSTR(CustomerName, 1 ,INSTR(CustomerName, ' ', 1, 1)-1) as first_name, SUBSTR(CustomerName, INSTR(CustomerName,' ',1,1)) as last_name from yourTableName
(oracle)
精彩评论