SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Version Breakdown


Version Breakdown

Author
Message
JKSQL
JKSQL
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 700
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


Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15490 Visits: 18622
Quick question, what is the driver for improvements if this works?
Cool
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16983 Visits: 10089
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
JKSQL
JKSQL
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 700
I gotta stop using loops! I know the tally works better. Thanks for the reminder. Worked great.
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16983 Visits: 10089
Glad I could help.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26614 Visits: 17557
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search