We help IT Professionals succeed at work.

split t-sql

NSGExchange
NSGExchange used Ask the Experts™
on
How do you split a column and place the split part into another column using T-sql.

i.e. The first and last name are in one column and you want the first name removed from the column and placed into another column that stores first names.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
Hello NSGExchange,

assuming that the names are separated by space

update urTable
set firstName = left(name, charindex(' ', name) -1 ) ,
      lastName  = substring( name, charindex(' ', name) , len(name)  )
where name like '% %'


Regards,

aneeshattingal
Top Expert 2010

Commented:
name
sanmo?mad

declare @name varchar(20)
set @name = 'sanmo?mad'

select left(@name, charindex('?', @name)-1), right(@name,
len(@name)-charindex('?', @name))

So something like this?

insert into dbo.Destination (fname, lname)
select left(fullname, charindex('?', fullname)-1), right(fullname,
len(fullname)-charindex('?', fullname))
from dbo.Source


output

name         lastname
sanmo       mad
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Hi, If there is special character in btn First name and last name than split the full name string and save it to different fields.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.