correct method to Use sub string in where clause mysql query

Question

I have a table in mysql with column called type. contents are below

Type
Test 12
Test abc
start 1
start abcd
end 123

Now I want to select records where type starts with Test

Expected result:

Test 12
Test abc

But I am getting either

Test 12 or empty results

I have tried like below:

select * from table where type = 'Test 12'
select * from table where type = '%Test%'
select * from table where type = '%Test'

What should be the correct sql statement.

tadman · Accepted Answer · 2017-09-25 20:04:00Z

2

If you want to do partial matches you need the LIKE operator:

SELECT * FROM table WHERE type LIKE 'Test%'

edited Sep 25, 2017 at 20:04

answered Sep 25, 2017 at 18:21

tadman

212k23 gold badges237 silver badges266 bronze badges

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

2 Comments

Jacques Amar Over a year ago

%Test ends with Test and not starts with it.

tadman Over a year ago

@JacquesAmar Corrected.

isaace · Accepted Answer · 2017-09-25 18:21:43Z

1

Use the Like keyword

select * from table where type LIKE 'Test%'

answered Sep 25, 2017 at 18:21

isaace

3,4291 gold badge11 silver badges26 bronze badges

Comments

Mureinik · Accepted Answer · 2017-09-25 18:22:01Z

1

The equality (=) operator doesn't accept wild cards. You should use the like operator instead:

SELECT * FROM table WHERE type LIKE 'Test%'

answered Sep 25, 2017 at 18:22

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Comments

Jacques Amar · Accepted Answer · 2017-09-25 18:22:06Z

1

Sooo close! You want to start with the string, then don't use % in the start, and use LIKE instead of =

select * from table where type LIKE 'Test%'

answered Sep 25, 2017 at 18:22

Jacques Amar

1,8231 gold badge12 silver badges12 bronze badges

Collectives™ on Stack Overflow

correct method to Use sub string in where clause mysql query

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related