Click here to Skip to main content
11,496,146 members (451 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
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
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
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 Sergey Alexandrovich Kryukov 10,401
1 OriginalGriff 8,910
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 23 Jul 2012
Copyright © CodeProject, 1999-2015
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