Dealing with charindex output

Question

Post reply

Dealing with charindex output

Deepthy

SSCommitted

Points: 1823
More actions
September 23, 2010 at 2:44 am

#228157

I have two tables TableA and Reference table TableB
DECLARE @TableA
TABLE (
city VARCHAR(50) NOT NULL
Clean_City varchar(50) NOT NULL
);
INSERT @TableA
(city)
VALUES ('London'),
('Ecblondontsr'),
('sdclondon'),
('londontres'),
('thichicago'),
('chicago')
('3450 Mumbai')
('-1-Mumbai3333');
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')
('MUMBAI','Mumbai')
('NEWYORK','New York');
I want to find out if city in TableA is in any part of Input_city of TableB, and if there is set cleancity of TableA to output_city of TableB.
My result Table TableA should be like
TableA
INSERT @TableA
(city,clean_city)
VALUES ('London', 'London'),
('Ecblondontsr','London'),
('sdclondon','London'),
('londontres','London'),
('thichicago','Chicago'),
('chicago','Chicago')
('3450 Mumbai','Mumbai')
('-1-Mumbai3333','Mumbai');
I can get the number at which the city is in Input_city using charindex(city,input_city) by joining the two tables, but no idea how to update the tableA with the output_city for each row of match.
Any help is highly appreciated.
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

rob_dalzell Old Hand Points: 315 More actions · Answer 1

/* If I understand correctly, this may produce the desired result */

UPDATE @TableA

SET Clean_City = B.output_city

FROM @TableA AS A

JOIN @TableB AS B ON CHARINDEX(B.input_city, A.city) > 0

Deepthy SSCommitted Points: 1823 More actions · Answer 2

Thanks for the reply.

Actually my reference table contains 2 million records. So I dont want to use join now as it is giving inappropriate results.

I tried something like this

Select * from tablea

where exists

(Select * from tableb where charindex(input_city,city)>0

but want to update the clean_city with in the same query.

Thanks in advance

Paul White SSC Guru Points: 150457 More actions · Answer 3

Haven't I answered this same question before from you?

http://qa.sqlservercentral.com/Forums/Topic987990-392-1.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Deepthy SSCommitted Points: 1823 More actions · Answer 4

The wildcard solution i tried before is not working in the way I want.

So switched to Charindex.

I am nearly there.

Select * from tablea

where exists

(Select * from tableb where charindex(input_city,city)>0)

I also want to see input_city in my output with out joing Table a AND tABLE b

Thanks,

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

Deepthy (9/23/2010)
The wildcard solution i tried before is not working in the way I want.

Can you explain why? I'm struggling to see a reason to prefer CHARINDEX. It would be nice to fully understand the issues so you get the answer you need.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Deepthy SSCommitted Points: 1823 More actions · Answer 6

Yeah Sure Paul

Suppose my tableA conatins Values like

insert tableA

(city)

values ('Pleasanton'),

('Ven');

My reference TableB contains values like

insert @TableB

(input_city,output_city)

values ('LEA' 'Albert Lea'),

(PLEASANTON','Pleasanton')

('VEN',Belgium)

('ZAVENTUM',Zaventum);

then our update statement might update city Pleasanton with output_city Albert Lea

and City Ven with Zaventum.

So trying to find an alternative method.

Any help is highly appreciated.

Thanks