[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Convert . seperated string into a bigint as a user-defined Function

Unfortunately we use PVCS, will be for a while longer. We have a home grown tracking system using MSSQL as a backend.  I have revision information stored as a string in the formats:
1.2   (no branches)
1.2.1.1  (branched once)
1.509  (no branches, heaps of revisions)
1.37.1.2.1.1  (branched 3 times)

I would like to be able to convert these strings into numbers so that I can do a straight comparison.  Due to the size of the fields, I'll end up with 3 digits for each, which ends up as a very big number.
001002000000000000
001002001001000000
001509000000000000
001037001002001001

I don't believe any single file has been branched more than 3 times, and I don't plan to cater for it.  I need a user-defined function that can take these formats and return a single number that I can then use easily for comparisons.
0
Hecatonchires
Asked:
Hecatonchires
  • 3
  • 2
1 Solution
 
SharathData EngineerCommented:
check this one.
select YourColumn,
       left(replace('.' + rtrim(left(YourColumn,charindex(' ',YourColumn))),'.','00') + '000000000000000',18)
 from YourTable

Open in new window

0
 
HecatonchiresAuthor Commented:
Unfortunately it just returns 17 0's (when run in query analyzer)
SELECT     revision, LEFT(REPLACE('.' + RTRIM(LEFT(revision, CHARINDEX(' ', revision))), '.', '00') + '000000000000000', 18) AS EXPR1
FROM         business_requirement_file
WHERE     (business_requirement_id = 82)
 
1.6	00000000000000000
1.10	00000000000000000
1.1	00000000000000000
1.2	00000000000000000
1.1	00000000000000000
1.0	00000000000000000
1.0	00000000000000000
1.0	00000000000000000
1.0	00000000000000000
1.1	00000000000000000
1.1	00000000000000000
1.0	00000000000000000
1.1	00000000000000000
1.1	00000000000000000
1.1	00000000000000000
1.0	00000000000000000
1.0	00000000000000000
1.1	00000000000000000
1.1	00000000000000000
1.1	00000000000000000
1.2	00000000000000000
1.13	00000000000000000
1.0	00000000000000000
1.1	00000000000000000
1.0	00000000000000000
1.1	00000000000000000
1.70.1.5	00000000000000000
1.0	00000000000000000
1.51	00000000000000000
1.1	00000000000000000
1.0	00000000000000000
1.2	00000000000000000
1.1	00000000000000000
1.0	00000000000000000
1.2	00000000000000000
1.0	00000000000000000
1.0	00000000000000000
1.409	00000000000000000

Open in new window

0
 
reb73Commented:
Try the following code -
/*
alter function dbo.formatversion(@input varchar(25))
returns varchar(18)
as
begin
	declare @pos1 int, @newvar varchar(100)
	set @pos1 = charindex('.',@input)
	while @pos1 > 0
	begin
		set @newvar = isnull(@newvar,'') + right('00' + substring(@input,1,@pos1-1),3)
		select @input = substring(@input,@pos1+1,len(@input)), @pos1= charindex('.',@input)
	end
	set @newvar = isnull(@newvar,'') + right('00' + @input,3)
	if len(@newvar) < 18
		select @newvar = @newvar + replicate('0',18-len(@newvar))
	return @newvar
end
*/
select dbo.formatversion('1.2')
union
select dbo.formatversion('1.2.1.1')
union
select dbo.formatversion('1.509')
union
select dbo.formatversion('1.37.1.2.1.1')

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
reb73Commented:
The function returns a varchar(18) as a bigint will have leading zeroes trimmed.. If that's ok, then you can do a cast of the returned value to bigint..
0
 
HecatonchiresAuthor Commented:
Thankyou very much for the answer Reb
0
 
HecatonchiresAuthor Commented:
As suggested have cast(@newvar to bigint) in the final solution.  Now I can safely compare different versions of the same file in different patches going into the same release.  Once again EE to the rescue ^_^
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now