CODEWITHSUNDEEP
sqlsql-server-2008
Richard
Richard

Reputation: 53

Update using case in two fields

I need to use the query below in an update in order to update the first and last name. What's the best option to do that?

SELECT  
person.fullName,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName) 
    then  person.fullName 
    ELSE SUBSTRING(person.fullName, 1, CHARINDEX(' ', person.fullName)) end) as first_name,  
(CASE WHEN 0 = CHARINDEX(' ', person.fullName) 
    THEN ''  
    ELSE SUBSTRING(person.fullName,CHARINDEX(' ', person.fullName), LEN(person.fullName) )end) last_name
FROM person

Thank you.

Upvotes: 1

Views: 42

Answers (1)

Abhilash R Vankayala
Abhilash R Vankayala

Reputation: 693

Please Try this code, i did not modify your condition , but showing you the logic to use your code to update first and last name. I assume the table has FirstName and LastName columns

UPDATE Person
SET
Person.FirstName =
(CASE WHEN 0 = CHARINDEX(' ', person.fullName) 
    then  person.fullName 
    ELSE SUBSTRING(person.fullName, 1, CHARINDEX(' ', person.fullName)) end)       ,  
Person.LastName =
(CASE WHEN 0 = CHARINDEX(' ', person.fullName) 
    THEN ''  
    ELSE SUBSTRING(person.fullName,CHARINDEX(' ', person.fullName), LEN(person.fullName) )end)
FROM person

Upvotes: 1

Related Questions