Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 SQL-server-2005 , +
I am facing problem while performing wildcard search in inner join.
 
I have two tables as follows
 
first one is
 
Code	Name
IN.01	Andaman 
IN.02	Andhra Pradesh
IN.03	Assam
IN.05	Chandīgarh
IN.06	Dādra 
IN.07	NCT
IN.09	Gujarāt
IN.10	Haryana
 
second one is
 
StateCode
 02
 10
 11
 12
 13
 14
 16
 
I want to write a query which will compare StateCode with Code and replace StateCode with corresponding Name and give me output like this
 

SatateCode    Name 
 10          Haryana
 02          Andhra Pradesh
 
and so on......
 

 
Any alternative approach would be great helpful.
Posted 14-Mar-12 1:45am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If first one is T1 & second one is T2 then this query will be:
 
SELECT T2.statecode as SatateCode,T1.Name from T1 innerjoin T2 where T2.StateCode== CONVERT(INT, Replace(T1.StateCode,'IN.','')
 
I think its your solution !
  Permalink  
Comments
vikram_shinde at 14-Mar-12 7:12am
   
StateCode are nvarchar . So still I have to use CONVERT?
uuttam-kumar at 14-Mar-12 7:25am
   
then its so simple like this:
SELECT T2.statecode as SatateCode,T1.Name from T1 inner join T2 where T2.StateCode== Replace(T1.StateCode,'IN.','')
vikram_shinde at 14-Mar-12 7:30am
   
no luck .... it's giving error... says Incorrect syntax near the keyword 'where'
uuttam-kumar at 14-Mar-12 7:41am
   
SELECT T2.statecode as SatateCode,T1.Name from T1 inner join T2 on T2.StateCode== Replace(T1.StateCode,'IN.','')
 
Sorry..i missed 'ON'
good luck
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

UPDATE T1.Name = T2.StateCode
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.StateCode==REPLACE(T1.StateCode,'IN.','')
 
-- Try This, Its helps u.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 505
1 Maciej Los 325
2 Richard MacCutchan 265
3 Mathew Soji 220
4 BillWoodruff 210
0 OriginalGriff 8,804
1 Sergey Alexandrovich Kryukov 7,457
2 DamithSL 5,689
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411028.1 | Last Updated 23 Jul 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100