Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Version Breakdown Expand / Collapse
Author
Message
Posted Friday, May 9, 2014 10:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 420, Visits: 605
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

Post #1569379
Posted Friday, May 9, 2014 10:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 1,530, Visits: 4,235
Quick question, what is the driver for improvements if this works?
Post #1569380
Posted Friday, May 9, 2014 10:49 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 4,132, Visits: 3,159
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;




Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1569385
Posted Friday, May 9, 2014 10:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 420, Visits: 605
I gotta stop using loops! I know the tally works better. Thanks for the reminder. Worked great.
Post #1569386
Posted Friday, May 9, 2014 11:07 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 4,132, Visits: 3,159
Glad I could help.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1569391
Posted Friday, May 9, 2014 12:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 13,224, Visits: 12,068
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1569407
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse