Column Comparision

Question

Post reply

Column Comparision

Deepthy

SSCommitted

Points: 1823
More actions
September 17, 2010 at 2:54 am

#228010

I have been working on this since yesterday. could not find a way out.
I have two tables TableA and TableB
TableA
City
----
London
Ecblondontsr
sdclondon
londontres
thichicago
chicago
TableB
Input_city Output_City
---------- -------------
LONDON London
GREATER LONDON London
CITY OF LONDON London
CHICAGO Chicago
All I want is compare city column of TableA with Input_city column of TableB and set the city to output_city if they are equal.
Here equal means
ex: Ecnlondonstr contain LONDON in between, even though it should set to output_city
similarly sdclondon should also set to London as the city contains Input_city LONDON some where.
On a whole something like this
when city = %input_city
then set city = output_city.
when city= %input_city%
then set city = output_city
when city = input_city%
then set city = output_city.
But not sure how to use wildcard with column
Any help is highly appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Deepthy SSCommitted Points: 1823 More actions · Answer 1

SORRY Small change

On a whole something like this

when city LIKE %input_city

then set city = output_city.

when city LIKE %input_city%

then set city = output_city

when city LIKE input_city%

then set city = output_city.

crazy4sql SSCoach Points: 19590 More actions · Answer 2

have you tried by this :-

update table1 set city = a.inputcity

from table2 a

where ...... whatever condition you have

----------
Ashish

Deepthy SSCommitted Points: 1823 More actions · Answer 3

Thanks for the reply.

I have tied that,

but the thing is I dont have any idea of framing my condition, which need % with column name

crazy4sql SSCoach Points: 19590 More actions · Answer 4

if you can explain further that what exactly your table should be look like after running the query, i can try to write the query

----------
Ashish

Paul White SSC Guru Points: 150468 More actions · Answer 5

DECLARE @TableA

TABLE (

city VARCHAR(50) NOT NULL

);

INSERT @TableA

(city)

VALUES ('London'),

('Ecblondontsr'),

('sdclondon'),

('londontres'),

('thichicago'),

('chicago');

DECLARE @TableB

TABLE (

input_city VARCHAR(50) NOT NULL,

output_city VARCHAR(50) NOT NULL

);

INSERT @TableB

(input_city, output_city)

VALUES ('LONDON', 'London'),

('GREATER LONDON', 'London'),

('CITY OF LONDON', 'London'),

('CHICAGO', 'Chicago');

UPDATE A

SET city =

(

SELECT B.input_city

FROM @TableB B

WHERE A.city LIKE '%' + B.input_city + '%'

)

FROM @TableA A

WHERE EXISTS

(

SELECT *

FROM @TableB B

WHERE A.city LIKE '%' + B.input_city + '%'

);

SELECT *

FROM @TableA;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Deepthy SSCommitted Points: 1823 More actions · Answer 6

My final table TableA shoulb be like this

TableA

City

-----

London

Chicago

Paul White SSC Guru Points: 150468 More actions · Answer 7

For your next question, try to provide sample data in the format I used, and be sure to include what you expect as output - it makes it easier for us, and gets you the answer you want faster 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Deepthy SSCommitted Points: 1823 More actions · Answer 8

Sure Paul I will from next time.

I'll have a try on the solution u provide.

Thanks a lot.

Paul White SSC Guru Points: 150468 More actions · Answer 9

No worries 🙂

If you are at all unsure how best to write a question, please take a look at this:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi