Click here to Skip to main content
12,502,116 members (56,591 online)
Rate this:
 
Please Sign up or sign in to vote.
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 0: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 14-Mar-12 7:12am
   
StateCode are nvarchar . So still I have to use CONVERT?
uuttam-kumar 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 14-Mar-12 7:30am
   
no luck .... it's giving error... says Incorrect syntax near the keyword 'where'
uuttam-kumar 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 23 Jul 2012
Copyright © CodeProject, 1999-2016
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