MySQL query (condition)

Question

I want to achieve one thing though I'm not sure if it's possible. So let's say I have a table with few columns, but only two of them are of interest to me right now. Example of table:

Column 1  | Column 2 
blabla    | blablahhhhh
wor154    | blablahhhhh
word123   | word12435564
something | some4565

What I want to achieve, is to select all fields where first 5 or more symbols of value of Column 2 don't match with first 5 or more symbols of value of Column 1. So I don't want to select rows where 5 or more symbols of value of Column 1 match 5 or more symbols of value of Column 2. In example, query should return only 2nd and 4th rows So, is it possible and if it's, how it can be achieved. Thank you.

You want to return them or not return them? Rows 2 & 4 above both match that condition, but you say you want the ones that don't match. — Michael Berkowski
– Michael Berkowski, Commented Oct 25, 2012 at 11:12
Is it certain that both fields will have length at least 5 characters? If not, how would your selection criterion apply when one or both fields have fewer characters (shorter strings)? — hardmath
– hardmath, Commented Oct 25, 2012 at 11:26
hardmath, even if it's not certain and and Col1=a and Col2=b, query below will select it, if Col1=a and Col2=a it won't so it works as it's supposed to. — Igor Yavych
– Igor Yavych, Commented Oct 25, 2012 at 11:39

Bart Friederichs · Accepted Answer · 2012-10-25 11:12:49Z

3

I'd go with a SUBSTRING():

SELECT col1 FROM table WHERE SUBSTRING(col1, 1, 5) <> SUBSTRING(col2, 1, 5);

answered Oct 25, 2012 at 11:12

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Berkowski Over a year ago

Even easier with LEFT(Col1, 5)

Taryn · Accepted Answer · 2012-10-25 11:13:01Z

3

You can use something similar to this:

select *
from table1
where substring(column1, 1, 5) != substring(column2, 1, 5)

See SQL Fiddle with Demo

answered Oct 25, 2012 at 11:13

Taryn

249k57 gold badges374 silver badges409 bronze badges

1 Comment

ozahorulia Over a year ago

There is no '!=' in MySQL, as I remember. Use <> instead.

Collectives™ on Stack Overflow

MySQL query (condition)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related