CODEWITHSUNDEEP
sql-server-2005
StackTrace
StackTrace

Reputation: 9416

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.

Upvotes: 0

Views: 604

Answers (3)

Juan P. Osio
Juan P. Osio

Reputation: 475

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)

Upvotes: 0

mjv
mjv

Reputation: 75095

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

Upvotes: 1

Lima
Lima

Reputation: 1211

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

Upvotes: 1

Related Questions