sql server compare two field

Question

5.00/5 (1 vote)

See more:

SQL-server-2000

SQL-Server-2005

, +

I am facing problem while performing wildcard search in inner join.

I have two tables as follows

first one is

SQL

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

SQL

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

SQL

SatateCode    Name 
 10          Haryana
 02          Andhra Pradesh

and so on......

Any alternative approach would be great helpful.

Posted 14-Mar-12 0:45am

vikram_shinde

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

uttam2010 · Answer 1 · 2012-03-14T01:05:00

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 !

Posted 14-Mar-12 1:05am

uttam2010

Comments

vikram_shinde 14-Mar-12 7:12am

StateCode are nvarchar . So still I have to use CONVERT?

uttam2010 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'

uttam2010 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

gvprabu · Answer 2 · 2012-07-22T23:45:00

Solution 2

SQL

UPDATE T1.Name = T2.StateCode
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.StateCode==REPLACE(T1.StateCode,'IN.','')

-- Try This, Its helps u.

Posted 22-Jul-12 23:45pm

gvprabu