String Comparison in sql server 2005 2

[mtrasp]

DECLARE @VistaLimit VARCHAR(20)
DECLARE @Win7Limit VARCHAR(20)
DECLARE @VistaWin7Limit VARCHAR(20)
DECLARE @RelName VARCHAR(20)



SELECT @RelName = [Name] FROM Table name WHERE ReleaseNameID = 3956 ProductGroupID = 30

IF @RelName <=15.14
BEGIN
print 'here'

END
ELSE IF @RelName =15.15
BEGIN
print 'here1'

END
ELSE IF @RelName >=15.16
BEGIN
print 'here2'

END
ELSE
BEGIN
print'here3'

END

here my problem is How can i compare @Relname with given values .@RelName values are coming like 15.16,15.win7, 15.12win7,15.11win7....etc.. please help how first loop is executing when @Relname is 15.12win7

 

[markros]

There is something wrong already. You declared @RelName as a character, but trying to compare it with the float (or decimal) type.

You need to compare character with character, e.g.

@RelName <='15.14'

or convert @RelName to decimal.

Now, can you please re-state your problem?


PluralSight Learning Library

 

[mtrasp]

Thank you for your reply


DECLARE @Win7Limit VARCHAR(20)
DECLARE @VistaWin7Limit VARCHAR(20)
DECLARE @RelName VARCHAR(20)


SET @VistaLimit = 15.14
SET @Win7Limit = 15.15
SET @VistaWin7Limit = 15.16



SELECT @RelName = [Name] FROM Table WHERE NameID = 3956

IF @RelName <=@VistaLimit
BEGIN
print 'here'

END
ELSE IF @RelName =@Win7Limit
BEGIN
print 'here1'

END
ELSE IF @RelName >=@VistaWin7Limit
BEGIN
print 'here2'


END
ELSE
BEGIN
print'here3'

END


Here if @RelName is 15.12win7 then it is comapring with @vistaLimit (15.14) and printing 'here'
if @RelName is 15.win7 then it is comapring with @vistaWin7Limit (15.16) an dprinting 'here2'

my problems is how 15.12win7 is comparing with 15.14 because 15.12win7 is alphanumeric
second problem is when @Relname is 15.win7 i want to execute second loop i.e i want to print 'here1'
please help me how this comaprison is happening

 

[markros]

Sorry, I still don't get it, but please change

SET @VistaLimit = 15.14
SET @Win7Limit = 15.15
SET @VistaWin7Limit = 15.16

to

SET @VistaLimit = '15.14'
SET @Win7Limit = '15.15'
SET @VistaWin7Limit = '15.16'

PluralSight Learning Library

 

[gmmastros]

You comparisons are done based on strings.

For example, which should come first, 9 or 10? If you compare them as a number, the 9 should come first, but if you compare them as strings, the 10 would come first.

Copy/paste this to a query window and run it.

Declare @Temp Table(Data VarChar(10))

Insert Into @Temp Values('9')
Insert Into @Temp Values('10')

Select * From @Temp Order By Data

String comparisons (and ordering) is actually based on collations within SQL Server. Collations are a relatively large and somewhat complicated topic. Basically, though, you can simplify this to be.... ASCII sorting.

With string sorting, each character is sorted separately from left to right. In the 9 versus 10 issue (when sorting as a string), the first characters are sorted so the 9 is sorted relative to the 1 (from the 10). Then the second characters is sorted. This is no second character for the 9, but there is for the 10. No character sorts before any character. With string sorts, characters sort AFTER numbers. Expanding on the earlier example...

Declare @Temp Table(Data VarChar(10))

Insert Into @Temp Values('9')
Insert Into @Temp Values('10')
Insert Into @Temp Values('a')
Insert Into @Temp Values('1')
Insert Into @Temp Values('100')

Select * From @Temp Order By Data

Results:
[tt][blue]
Data
----
1
10
100
9
a
[/blue][/tt]

I hope the "WHY" part of your question is answered. Now, on to the "How do I" part. I recommend that you create another table in your database. This table should contain exactly one row for each distinct relname/version number. This table will require a bit of maintenance from time to time. Specifically, when you create a new version, you'll need to add a new row to this table. By adding this table, you will suddenly make your life a lot easier, more flexible, and probably faster too.

To create the table....

Create Table VersionNumbers(Name VarChar(20), AnotherColumn VarChar(20))

Insert Into VersionNumbers(Name) Select Distinct Name From [table]

This is just a brief mock up. You'll want to make sure you have an index on the name column in this table. In fact, you'll probably want this to be a primary key.

Anyway.... after you create the table, you can put any value you want in for the "AnotherColumn" column (Here, Here2, Here3, etc...)

Then, all of the code you had before would become this:

SELECT [table].[Name], VersionNumbers.AnotherColumn
FROM   [Table]
       Left Join VersionNumbers
         On [table].[name] = VersionNumbers.[name]
WHERE  [table].NameID = 3956

Once you have this table, you will likely find lots of uses for it. For example, you could create another column for the "Major Version Number". This column would probably be an integer. You could create another column for "Minor Version Number", Windows Operating System, Release Date, etc.... Basically, this new table will remove the burden of trying to make meaningful sense of out a column that appears to be storing (at least) 3 separate pieces of information.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mtrasp]

Thank you very much for your valuable Help. I am trying to implement what ever you said in my problem Really Thank You verymuch

Yours sincerely
mtr

 

[mtrasp]

Thank you for your valuable suggestion can u please see the below one..
Instead of creating new Table can i use Temporary Table. below code is executing in while loop in one stored procedure..


----stored procedure starts here----

while ()

DECLARE @VistaLimit VARCHAR(20)
DECLARE @Win7Limit VARCHAR(20)
DECLARE @VistaWin7Limit VARCHAR(20)
DECLARE @RelName VARCHAR(20)


SET @VistaLimit = '15.14'
SET @Win7Limit = '15.15'
SET @VistaWin7Limit = '15.16'

DECLARE @OSGROUPTAB TABLE
(
OSGroupID INT
)




SELECT @RelName = [Name]
FROM Table
WHERE ReleaseNameID = 3273
AND ProductGroupID = 56
AND [Name] Like '15.%'

IF @RelName <=@VistaLimit
BEGIN
print 'here'
INSERT INTO @OSGROUPTAB
SELECT OSGroupID FROM .t_OSGroup WHERE OsGroupName LIKE 'WinVista%'
END
ELSE IF @RelName =@Win7Limit
BEGIN
print 'here1'
INSERT INTO @OSGROUPTAB
SELECT OSGroupID FROM .t_OSGroup WHERE OsGroupName LIKE 'Win7%'
END
ELSE IF @RelName >=@VistaWin7Limit
BEGIN
print 'here2'
INSERT INTO @OSGROUPTAB
SELECT OSGroupID FROM .t_OSGroup WHERE OsGroupName LIKE 'Win7%' OR
OsGroupName LIKE 'WinVista%'
END
ELSE
BEGIN
print'here3'
INSERT INTO @OSGROUPTAB
SELECT OSGroupID FROM .dbo.t_OSGroup
END



cursor executes here.. it take the osgroupid from @OSGROUPTAB TABLE


end while loop


As you said I created VersionNumber Table. from the business functionality i came to know that if @RelName is 15.win7 i can update it to 15.15 in anothercoloumn (in VersionNumber table). but i dont know what numbers i need to update for remaining @Relnames.so iam planning to create temporary table (is that good idea?)and if @Relname is 15.win7 i need to assign 15.15 and i need to handle @Relnames like 15.12win7,15.11win7,15.16,15.13.. etc to compare. Sorry for confusing but i need your Help.

 

[gmmastros]

When you post code, you should use code blocks because it makes the code easier to read. When writing a post, do this....


[ignore]

Put your code here

[/ignore]

Anyway.... I would still encourage you to use a real table for this. If you decide to use a table variable, and another version is added later, you will need to modify code to accommodate the new version.

If you take my advice and create a real table, then all you need to do is add a new row to the table. You could even build a front end for this table so that "any ole" person can maintain it. Since you probably don't have all that many versions, maintaining this table should be relatively easy. Furthermore, making this a real table has other advantages, like... you can create indexes on this table to improve performance. And like I said earlier, you are very likely to find other uses for this table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom