May 9, 2014 at 10:36 am
Currently we store our version as a string in a table. I need to break this into ints for comparison. Below is the code that functionally works the way I want it to. Is there a better pattern I should use?
DECLARE @Version Varchar(50),
@dbReleaseIndex smallint, @dbMajor smallint , @dbMinor smallint ,@dbBuild smallint ,
@dbServicePack smallint ,@dbRevision smallint , @i tinyint=0,@DotIndex tinyint,@LastDotIndex tinyint
SET @Version = '107.1055.0403.1001.110.1011'+'.'
WHILE @i <=5
BEGIN
IF @i=0
BEGIN
SET @DotIndex = CHARINDEX('.',@version,1)
SET @dbReleaseIndex = SUBSTRING(@Version,1,@DotIndex-1)
SET @LastDotIndex = @DotIndex+1
END
IF @i=1
BEGIN
SET @DotIndex = CHARINDEX('.',@version,@LastDotIndex)
SET @dbMajor = SUBSTRING(@Version,@LastDotIndex,@DotIndex-@LastDotIndex)
SET @LastDotIndex = @DotIndex+1
END
IF @i=2
BEGIN
SET @DotIndex = CHARINDEX('.',@version,@LastDotIndex)
SET @dbMinor = SUBSTRING(@Version,@LastDotIndex,@DotIndex-@LastDotIndex)
SET @LastDotIndex = @DotIndex+1
END
IF @i=3
BEGIN
SET @DotIndex = CHARINDEX('.',@version,@LastDotIndex)
SET @dbBuild = SUBSTRING(@Version,@LastDotIndex,@DotIndex-@LastDotIndex)
SET @LastDotIndex = @DotIndex+1
END
IF @i=4
BEGIN
SET @DotIndex = CHARINDEX('.',@version,@LastDotIndex)
SET @dbServicePack = SUBSTRING(@Version,@LastDotIndex,@DotIndex-@LastDotIndex)
SET @LastDotIndex = @DotIndex+1
END
IF @i=5
BEGIN
SET @DotIndex = CHARINDEX('.',@version,@LastDotIndex)
SET @dbRevision = SUBSTRING(@Version,@LastDotIndex,@DotIndex-@LastDotIndex)
SET @LastDotIndex = @DotIndex+1
END
SET @i= @i+1
END
SELECT @Version as TheVersion,
@dbReleaseIndex as dbReleaseIndex,
@dbMajor as dbMajor,
@dbMinor as dbMinor,
@dbBuild as dbBuild,
@dbServicePack as dbServicePack,
@dbRevision as dbRevision
May 9, 2014 at 10:41 am
Quick question, what is the driver for improvements if this works?
😎
May 9, 2014 at 10:49 am
This uses Jeff Moden's DelimitedSplit8K function posted at http://www.sqlservercentral.com/articles/Tally+Table/72993/. If you aren't familiar with it yet, take the time to acquainted with it. It's well worth the read and will change your expectations of performance when it comes to splitting strings.
create table test (
version varchar(50) not null);
insert into test(version) values('107.1055.0403.1001.110.1011');
insert into test(version) values('108.1055.0403.0110.001.0100');
select version,
release = MAX(case when s.itemnumber = 1 then s.item end),
major = MAX(case when s.itemnumber = 2 then s.item end),
minor = MAX(case when s.itemnumber = 3 then s.item end),
build = MAX(case when s.itemnumber = 4 then s.item end),
service_pack = MAX(case when s.itemnumber = 5 then s.item end),
revision = MAX(case when s.itemnumber = 6 then s.item end)
from test
cross apply DelimitedSplit8K(version, '.') s
group by version;
May 9, 2014 at 10:58 am
I gotta stop using loops! I know the tally works better. Thanks for the reminder. Worked great.
May 9, 2014 at 11:07 am
May 9, 2014 at 12:15 pm
JKSQL (5/9/2014)
I gotta stop using loops! I know the tally works better. Thanks for the reminder. Worked great.
If possible why not break this into separate columns? Saves a lot of headaches when you have proper normalization. You are using a single column to hold 6 values. Then if you want to have the full version put together you could use a computed column.
Something like this would be a lot easier to work with.
create table AppVersion
(
Release smallint not null,
Major smallint not null,
Minor smallint not null,
Build smallint not null,
ServicePack smallint not null,
Revision smallint not null,
FullVersion as cast(Release as varchar) + '.'
+ cast(Major as varchar) + '.'
+ cast(Minor as varchar) + '.'
+ cast(Build as varchar) + '.'
+ cast(ServicePack as varchar) + '.'
+ cast(Revision as varchar)
)
insert AppVersion
select 107, 1055, 0403, 1001, 110, 1011
select *
from AppVersion
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply