CODEWITHSUNDEEP
sqlpostgresql
Visahan
Visahan

Reputation: 1192

PostgreSQL Join not working with special Characters

I have a database table (for example Book) with the column name (character varying) part of it. I imported data from a csv to this table. Unfortunately, in the CSV's name column, almost all the rows contained a few special characters such as / and _.

Now I have another csv which contains 2 columns which are the invalid name column which was imported and the correct name value as the other column. I imported this into a new table I created, which after update I will delete. When I try to combine the tables to update, the columns with special characters do not show up.

select b.name from Book b JOIN tempBook tb on b.name= tb.invalid_name;

returns 0 rows though manual checking confirmed both values are same for a few hundred columns.

I cannot delete and re import data to the Book table because it's live data and other columns have been modified. Is there a way for me to query and match the tables where both matching columns contain special characters?

EDIT

Sample data

Book
HarryN/APotter

tempBook
HarryN/APotter

But the query added previously returns 0 results. I'm expecting the results to be 

HarryN/APotter

Upvotes: 1

Views: 986

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You probably have other unusual characters. I would replace the spaces in the valid name and use that for comparison:

select b.name
from Book b JOIN
     tempBook tb
     on tb.invalid_name like '%' || replace(b.name, ' ', '%') || '%';

This is not going to be very efficient. But, it can get you started on figuring out where the problems might lie.

Upvotes: 0

Related Questions