how to compare two strings - 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 2000 Forums

Transact-SQL (2000)

how to compare two strings

Author

Topic

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-06 : 10:59:50

Hi
I think i posted my previous thread on a wrong forum (SQLSERVER 2005).
I should have posted it in SQLSERVER 2000.I am sorry!

I want to compare two sets of data. could be either numbers or alphabets,

for example , if a table-A has following records(countrycode,price)
countrycode , price
44 , $10.0
447, $15.0
4476,$15.0

in another table-B i have
countrycode , price
44, $10.0
447, $14.0
4476,$14.0
4478,$12.0

If i compare these two tables on basis of countrycodes, and using outer join , this is what i would get

countrycode, table-A-price , table-B-price
44 , $10.0 , $10.0
447 , $15.0 , $14.0
4476 , $15.0 , $14.0
4478 , null , $12.0

As you can see since 4478 code did not existed in table-A i got null.
This is where i am facing problem. If a code does not exist in table A , I want to associate alternative price to it . Like 4478 did not existed nor it had any price but i want to assign 447 code price to 4478 as 447 is best match (Best match should be maximum number of digits matching from left ).

I would really appreciate your help on this one. Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 11:13:46

Use FULL JOIN on countrycode column.

E 12°55'05.63"
N 56°04'39.26"

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-06 : 11:50:00

Thanks for the update.
I do not follow FULL JOIN would get results from both tables.but how would it fetch the price by matching the closed match to missing code.

thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-07 : 05:21:45

select a.countrycode,a.price,b.price as priceval
from tablea a
full outer join
tableb b on b.countrycode = a.countrycode

select b.countrycode,a.price,b.price as priceval
from tableb b
left join
tableb a on b.countrycode = a.countrycode

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-07 : 06:03:35

Thanks for your reply. Are these two seprate queries or One.
when i ran the first top query this is what i got

countrycode,price,priceval
92, 10.0, 10.0
923, 11.0, 12.0
NULL, NULL, 11.0
NULL, NULL, 11.0
92300, 9.5, 10.0

this is showing what codes and prices are missing in this table if i join it with tableA

and when i ran the other i got the following
countrycode,price,priceval
92, 10.0, 10.0
923, 12.0, 12.0
92345, 11.0, 11.0
92333, 11.0, 11.0
92300, 10.0, 10.0

which is equivalent to select * from tableB

Either i am unable to grasp or possibly i failed to explain the problem.
please read the following thread. Perhaps it would explain more.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124404&SearchTerms=ahmadjamalkhan

I would appreciate if you could help me with this . Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-07 : 06:44:50

declare @t table (countrycode int, price VARCHAR(32))
insert into @t select 44 , '$10.0'
insert into @t select 447, '$15.0'
insert into @t select 4476,'$15.0'

declare @t1 table (countrycode int, price VARCHAR(32))
insert into @t1 select 44, '$10.0'
insert into @t1 select 447, '$14.0'
insert into @t1 select 4476,'$14.0'
insert into @t1 select 4478,'$12.0'

select b.countrycode,a.price,b.price as priceval
from @t a
full outer join
@t1 b on b.countrycode = a.countrycode

output as
countrycode price priceval
44 $10.0 $10.0
447 $15.0 $14.0
4476 $15.0 $14.0
4478 NULL $12.0

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-07 : 07:25:39

Yes as you can see from your result , for code 4478 from table t the price is NULL where from table t1 its 12.0
Now in this situation when a code is NULL ( like in this case 4478) I want to assign code 447 price with it .
Because 447 is closet match to 4478 , first 3 digits from left are exact match.
and This is where iam lost . I do not know how to construct a query which incase of NULL values looks for the codes above
, find the best match and assign its price for that code.

I hope i am making sense. The desired outout for above scenario should be like this

44 $10.0 $10.0
447 $15.0 $14.0
4476 $15.0 $14.0
4478 $15.0 $12.0

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources