CODEWITHSUNDEEP
mysqlsql
min2bro
min2bro

Reputation: 4628

Split and match string in MYSQL

I wanted to select all rows where range address's are equal

Table

  id    Address
    1   5419-5436 East hancock Street, Somerset, VA
    2   251 West Churchill Road, Arlington, TX
    3   981-981 Main Street , norman, OK
    4   615-615 Mountain Ave, Bridgewater, NJ

Output

id  Address
3   981-981 Main Street , norman, OK
4   615-615 Mountain Ave, Bridgewater, NJ

The address number 981-981,615-615 are equal. 5419-5436 is not selected since it's have different address range.

Upvotes: 0

Views: 330

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

select * from your_table where
SUBSTRING_INDEX( SUBSTRING_INDEX(Address,'-',-1),' ',1)=SUBSTRING_INDEX(Address,'-',1)

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

Try this:

Sample data:

create table tbl(txt varchar(100));
insert into tbl values
('5419-5436 East hancock Street, Somerset, VA'),
('251 West Churchill Road, Arlington, TX'),
('981-981 Main Street , norman, OK'),
('615-615 Mountain Ave, Bridgewater, NJ');

T-SQL below. In inner query we select whole address and the range address extracted and filter out those records that don't have hyphen (-). In outer query we split RangeAddress by hyphen (-) and compare to parts of RangeAddress. Based on that we filter resultset.

select txt from (
    select txt,
           substring(txt, 1, locate(' ', txt) - 1) RangeAddress
    from tbl
    where locate('-', substring(txt, 1, locate(' ', txt) - 1)) > 0
) a 
where substring(RangeAddress, 1, locate('-', RangeAddress) - 1) = 
                 substring(RangeAddress, locate('-', RangeAddress) + 1);

Upvotes: 1

Nick
Nick

Reputation: 147146

You can use SUBSTRING_INDEX to select the portions on either side of the '-' and compare them:

SELECT * 
FROM Table1
WHERE SUBSTRING_INDEX(Address, '-', 1) = SUBSTRING_INDEX(SUBSTRING_INDEX(Address, '-', -1), ' ', 1)

Output for your sample data:

id    Address
3     981-981 Main Street , norman, OK 
4     615-615 Mountain Ave, Bridgewater, NJ

Note this code assumes that the numbers are followed by a space.

Upvotes: 3

Related Questions