Sql query to split one column into two columns

Question

I have one column as the project name, data in the project name

1.1.1 chapter1 1.1.2 chapter2 

I want to split this single column into two columns as

 Major Minor 1.1 .1 chapter1 1.1 .2 chapter2 

The data type of my project name column is nvarchar, I am using sql 2005

Any help?

Answer 1

Something like that

 declare @x nvarchar(500) = '1.1.1 chapter1' select substring(@x,1,charindex('.',@x,1+charindex('.',@x))-1) as Major, substring(@x,charindex('.',@x,1+charindex('.',@x)),len(@x)-charindex('.',@x,1+charindex('.',@x))) as Minor 

Replace @x in the request.

and fiddle for him: http://sqlfiddle.com/#!3/d41d8/4424/0

updated with. ahead and proof of error

declare @x nvarchar (500) = '1.1.1 chapter1'

 select @x, case when charindex('.',@x,1+charindex('.',@x)) <> 0 then substring(@x,1,charindex('.',@x,1+charindex('.',@x))-1) else 'Cannot be parsed' end, case when charindex('.',@x,1+charindex('.',@x)) <> 0 then substring(@x,charindex('.',@x,1+charindex('.',@x)),len(@x)-charindex('.',@x,1+charindex('.',@x))+1) else 'Cannot be parsed' end 

and without. ahead

declare @x nvarchar (500) = '1.1.1 chapter1'

 select @x, case when charindex('.',@x,1+charindex('.',@x)) <> 0 then substring(@x,1,charindex('.',@x,1+charindex('.',@x))-1) else 'Cannot be parsed' end, case when charindex('.',@x,1+charindex('.',@x)) <> 0 then substring(@x,1+charindex('.',@x,1+charindex('.',@x)),len(@x)-charindex('.',@x,1+charindex('.',@x))) else 'Cannot be parsed' end 

http://sqlfiddle.com/#!3/d41d8/4430/0

Answer 2

 select substring(ProjectName,1,charindex('.',ProjectName,charindex('.',@t)+1)) as Major