CODEWITHSUNDEEP
sqlsql-server-2005
Ushma Rana
Ushma Rana

Reputation: 29

Sql query to split one column into two columns

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

1.1.1 chapter1
1.1.2 chapter2

I want to divide that single column into two columns as

Major   Minor
1.1     .1 chapter1
1.1     .2 chapter2

the datatype of my project name column is nvarchar, I am using sql 2005

Any help?

Upvotes: 0

Views: 1674

Answers (2)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

Something like this 

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

Substitute @x in your query ..

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

updated with the . in front and proof to 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 the . in front

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

Upvotes: 2

Haseeb Asif
Haseeb Asif

Reputation: 1786

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

Upvotes: 0

Related Questions