CODEWITHSUNDEEP
mysqlsql
user2924127
user2924127

Reputation: 6242

compare strings from two columns and make sure their substrings are not the same

I have two columns:

Location1   |  Location2
---------------------------
Canada*ab*av    USA*nm*mn
Canada*vn*mv    Canada*ty*av

I want to select all the rows where the first part of the string (before the first '*' ) are different. So in the example I would get the first row because Canada and USA are different, but I would NOT get the second row because CANADA and CANADA are the same.

I tried something like this, but it looks like it is a syntax error:

select * from tab
WHERE substring_index(location1, '*', 1) <> substring_index(location2, '*', 1)

Upvotes: 0

Views: 192

Answers (1)

Ryuhinata
Ryuhinata

Reputation: 66

select * from tab WHERE substring_index(location1, '', 1) <> substring_index(location1, '', 1)

Shouldn't you compare Location1 vs Location2? And i would suggest to keep the word case to be the same as your table. I.E. use Location instead of location.

Upvotes: 1

Related Questions