Name | |
user Name1 | xyz@abc.com |
user Name2 | xyz1@abc.com |
User Name 3 (Company Inc) | xyz2@abc.com |
Mr 123456 678 | xyz3@abc.com |
user Name4 123344 (Company Interactive 123) | xyz4@abc.com |
Marcelle XYZ | xyz5@abc.com |
Create a temp table and insert the data.
As you can see we may or may not have “braces” with company name.
Use the following code to split the first name, last name and company name.
--drop table temp Create table temp ( id int identity(1,1) ,Name nvarchar(500) ,Email nvarchar(100) ) insert into temp values ('user Name1','xyz4@abc.com'),('user Name4 123344 (Company Interactive 123)','xyz4@abc.com') select * from temp alter table temp add FirstName nvarchar(100),LastName nvarchar(100),Company nvarchar(100) DECLARE @Id int ,@Name nvarchar(1000) ,@FirstName nvarchar(100) ,@LastName nvarchar(100) ,@Company nvarchar(100) ,@sDelimiter nVARCHAR(100) DECLARE vendor_cursor CURSOR FOR select Id, Name from temp OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @Id, @Name WHILE @@FETCH_STATUS = 0 BEGIN select @FirstName = '' ,@LastName = '' ,@Company = '' SET @sDelimiter = ' ' IF CHARINDEX(@sDelimiter,@Name,0) <> 0 BEGIN SELECT @FirstName = RTRIM(LTRIM(SUBSTRING(@Name,1,CHARINDEX(@sDelimiter,@Name,0)-1))), @Name = RTRIM(LTRIM(SUBSTRING(@Name,CHARINDEX(@sDelimiter,@Name,0)+LEN(@sDelimiter),LEN(@Name)))) END SET @sDelimiter = '(' IF CHARINDEX(@sDelimiter,@Name,0) <> 0 BEGIN SET @LastName = RTRIM(LTRIM(SUBSTRING(@Name,1,CHARINDEX(@sDelimiter,@Name,0)-1))) set @Company = REPLACE (RTRIM(LTRIM(SUBSTRING(@Name,CHARINDEX(@sDelimiter,@Name,0)+LEN(@sDelimiter),LEN(@Name)))),')','') END ELSE SET @LastName = substring(@Name,charindex(' ', @Name)+1,len(@Name)) update temp set FirstName = @FirstName ,LastName = @LastName ,Company = @Company where Id = @Id FETCH NEXT FROM vendor_cursor INTO @Id, @Name END CLOSE vendor_cursor DEALLOCATE vendor_cursor
Finally, your temp table will have the separated columns.