0

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?

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Ushma Rana
  • 29
  • 1
  • 5

2 Answers2

2

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

Dumitrescu Bogdan
  • 7,127
  • 2
  • 23
  • 31
  • I got an error as "Invalid length parameter passed to the SUBSTRING function" this is ma query – Ushma Rana Sep 15 '12 at 08:04
  • If you pass instead of @x an varchar or nvarchar, it will work perfectly .. check the fiddle .. – Dumitrescu Bogdan Sep 15 '12 at 08:08
  • i checked ur fiddle, its running f9, but on my sql server 2005 i am getting error – Ushma Rana Sep 15 '12 at 08:49
  • Show me the query you are using. The query should work fine on sql 2005. Unless not all your strings look like you showed. I did not do any error checking .. so it will give an error if your strings are different in your query .. (they need at list 2 points) – Dumitrescu Bogdan Sep 15 '12 at 08:53
  • SELECT vProjectName, substring(vProjectName,1,charindex('.',vProjectName,charindex('.',vProjectName)+1)) as Major, substring(vProjectName,charindex('.',vProjectName,1+charindex('.',vProjectName)),len(vProjectName)-charindex('.',vProjectName,1+charindex('.',vProjectName))) as Minor from projects – Ushma Rana Sep 15 '12 at 08:59
  • the above query works absolutely f9, the only problem is in Minor column i want "1 chapter i am human" but i am getting output as ".1 chapter i", i am not getting the whole syntax – Ushma Rana Sep 15 '12 at 09:01
  • thank you very much, it worked for me, now i only want to give alias name using as for both columns, but it is giving me error – Ushma Rana Sep 15 '12 at 09:28
  • put as minor, or as major immediately after the end – Dumitrescu Bogdan Sep 15 '12 at 10:07
0
select substring(ProjectName,1,charindex('.',ProjectName,charindex('.',@t)+1)) as Major
Haseeb Asif
  • 1,766
  • 2
  • 23
  • 41