Help me comparing 2 values... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help me comparing 2 values...

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2008-10-22 : 13:04:04

I've to compare two values. Help me providing a optimal solution.

declare @val1 varchar(100),@val2 varchar(100)
select @val1 = '5.1.03',
@val2 = '5.1.2'

I want to compare the two values and based on that my result should come... like..

If @val1 > @val2
select '@val1 is great
else if @val1 = @val1
select 'values are equal'
else
select '@val2 is great'

If i compare the variables with varchar datatype , the results are not accurate always ...

suppose, if the values look like @val1 = '5.01.2', @val2 = '5.1.2', am getting '@val1greater than @val2'.
But actually i wanted the result to returned as 'values are equal'

Help me with a suitable method to compare my values...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 13:41:50

Try this;-

select 
case when ((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1))+
 ((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1))*10+             ((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1))*100>0
then '@val1 is greater than @val2'
when ((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1))+
 ((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1))*10+             ((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1))*100=0
then 'values are equal'
else '@val2 is greater than @val1'
end

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-22 : 14:54:49

declare @val1 varchar(100),@val2 varchar(100)
select @val1 = '5.11',
@val2 = '5.11'
if not exists(
SELECT
case when A>B or (a is not null and b is null )then '@val1 is greater'
when A<B or (a is null and b is not null ) then '@val2 is greater'
else 'values are equal' end
FROM(
SELECT
top 1 A.Val as A,B.val as B
FROM
(select [id] from [Bookings].[dbo].[ParseValues](@val1) union select [id] from [Bookings].[dbo].[ParseValues](@val2))un
left join [Bookings].[dbo].[ParseValues](@val1) A on un.[ID]=A.[id]
left join [Bookings].[dbo].[ParseValues](@val2) B on un.[ID]=B.[id]
where ISNULL(A.val,'')<>ISNULL(B.val,'')
order by un.[id] asc
)Z) select 'values are equal'
else
SELECT
case when A>B or (a is not null and b is null )then '@val1 is greater'
when A<B or (a is null and b is not null ) then '@val2 is greater'
else 'values are equal' end
FROM(
SELECT
top 1 A.Val as A,B.val as B
FROM
(select [id] from [Bookings].[dbo].[ParseValues](@val1) union select [id] from [Bookings].[dbo].[ParseValues](@val2))un
left join [Bookings].[dbo].[ParseValues](@val1) A on un.[ID]=A.[id]
left join [Bookings].[dbo].[ParseValues](@val2) B on un.[ID]=B.[id]
where ISNULL(A.val,'')<>ISNULL(B.val,'')
order by un.[id] asc
)Z

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-22 : 14:57:14

quote:

Originally posted by visakh16

Try this;-

select 
case when ((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1))+
 ((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1))*10+             ((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1))*100>0
then '@val1 is greater than @val2'
when ((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1))+
 ((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1))*10+             ((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1))*100=0
then 'values are equal'
else '@val2 is greater than @val1'
end

Visakh, Not sure if this would work with paramaters like select @val1 = '5.110'
@val2 = '5.110'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 00:17:07

in that case it might need slight tweaking


select 
case when COALESCE((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1),0)+
 COALESCE((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1),0)*10+            COALESCE ((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1),0)*100>0
then '@val1 is greater than @val2'
when COALESCE((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1),0)+
 COALESCE((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1),0)*10+             COALESCE((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1),0)*100=0
then 'values are equal'
else '@val2 is greater than @val1'
end

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-23 : 00:28:22

try this


DECLARE @val1 VARCHAR(100),
		@val2 VARCHAR(100)
		
DECLARE @I INT,
		@J INT
		
SELECT	@val1 = '5.110',
		@val2 = '5.110'
		
SELECT @val1 = REPLACE(@val1, '.0', '.')
WHERE @val1 LIKE '%.0[0-9]%'

SELECT @val2 = @val2
WHERE @val2 LIKE '%.0[0-9]%'

SELECT	@I = CONVERT(INT, REPLACE(@val1, '.', ''))
		, @J = CONVERT(INT, REPLACE(@val2, '.', ''))
		
IF @I > @J
SELECT '@val1 is great'
ELSE IF @I = @J
SELECT 'values are equal'
ELSE
SELECT '@val2 is great'


"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-23 : 05:07:46

quote:

Originally posted by visakh16

Try this;-

select 
case when ((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1))+
 ((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1))*10+             ((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1))*100>0
then '@val1 is greater than @val2'
when ((PARSENAME(@val1,1)*1)-(PARSENAME(@val2,1)*1))+
 ((PARSENAME(@val1,2)*1)-(PARSENAME(@val2,2)*1))*10+             ((PARSENAME(@val1,3)*1)-(PARSENAME(@val2,3)*1))*100=0
then 'values are equal'
else '@val2 is greater than @val1'
end

You can sum the values like this and compare



DECLARE @val1 VARCHAR(100),
		@val2 VARCHAR(100)
		
DECLARE @I INT,
		@J INT
		
SELECT	@val1 = '5.01.2',
		@val2 = '5.1.2'

select PARSENAME(@val1,1)*1+PARSENAME(@val1,2)+PARSENAME(@val1,3)
select PARSENAME(@val2,1)*1+PARSENAME(@val2,2)+PARSENAME(@val2,3)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources