CODEWITHSUNDEEP
mysqlsql
Raja
Raja

Reputation: 79

SQL SUBSTRING in WHERE condition

create table numbers (number varchar(10));
insert into numbers (number) values
('1234123452'),
('5532003644'), 
('1122330505'),                                      
('1103220311'),                                     
('1103000011'),
('1103020012');

Query:-

SELECT * FROM numbers 
WHERE SUBSTRING(Number,1,4) = SUBSTRING(Number,5,8)

Result:-

There are no results to be displayed.

Expected Result:

1234123452

Upvotes: 0

Views: 240

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

You could take advantage of REGEXP_LIKE here, assuming you are using MySQL 8+:

SELECT *
FROM numbers
WHERE REGEXP_LIKE(Number, '^(.{4})\\1';

The pattern ^(.{4})\\1 matches and captures the first four characters, then asserts that these same characters appear immediately afterward.

Upvotes: 0

Barmar
Barmar

Reputation: 782315

The third argument to SUBSTRING() is the length, not the ending position. So it should be:

SELECT * FROM numbers 
WHERE SUBSTRING(Number,1,4) = SUBSTRING(Number,5,4)

Upvotes: 3

Related Questions