432,651 Members | 1,757 Online
Need help? Post your question and get tips & solutions from a community of 432,651 IT Pros & Developers. It's quick & easy.

# How to split a text field into words

 P: 48 i have two tables in mysql with address fields i want to compare the first two words of the address fields in both tables. is there a function similar to SPLIT. i.e. Add1= 1023 elm street Add2= 1023 elm St i need a function that would return TRUE upon comparing the first two words. because comparing the whole fields will return False. Dec 8 '11 #1

Please Check your MYSQL Version if SUBSTRING AND LOCATE function support and besides:
The Query I have given you before wont work for logical reason:

Try this function
Expand|Select|Wrap|Line Numbers
1. SELECT a.*, b.* FROM a, b WHERE SUBSTR(a.ab,1,LOCATE(' ',a.ab,LOCATE(' ',a.ab)+1)) LIKE SUBSTR(b.ab,1,LOCATE(' ',b.ab,LOCATE(' ',b.ab)+1))
2. AND
3. SUBSTR(a.ab,1,LOCATE(' ',a.ab,LOCATE(' ',a.ab)+1)) NOT LIKE '' AND SUBSTR(b.ab,1,LOCATE(' ',b.ab,LOCATE(' ',b.ab)+1)) NOT LIKE '';
4.

4 Replies

 100+ P: 1,059 Try this query: Expand|Select|Wrap|Line Numbers SELECT a.*, b.* from tbla a, tblb b WHERE SUBSTRING(a.Addr,0,LOCATE(a.Addr,' ',LOCATE(a.Addr,' ')))  LIKE SUBSTRING(b.Addr,0,LOCATE(b.Addr,' ',LOCATE(b.Addr,' ')));   This will do fine except: if you have consecutive spaces then you wont get proper result Dec 14 '11 #2

 P: 48 i tried and i got this error: ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1. SELECT a.*, b.ESI from datatable1 a, esi_table b WHERE SUBSTRING(a.ADDRESS,0,' at line 1 Error Code: 1064 I am using MySQL Workbench 5.2 and my query is as follows: Expand|Select|Wrap|Line Numbers SELECT a.*, b.ESI from datatable1 a, esi_table b WHERE SUBSTRING(a.ADDRESS,0,LOCATE(a.ADDRESS,' ',LOCATE(a.ADDRESS,' ')))      LIKE SUBSTRING(b.ADDRESS,0,LOCATE(b.ADDRESS,' ',LOCATE(b.ADDRESS,' ')));   Dec 14 '11 #3

 100+ P: 1,059 Please Check your MYSQL Version if SUBSTRING AND LOCATE function support and besides: The Query I have given you before wont work for logical reason: Try this function Expand|Select|Wrap|Line Numbers SELECT a.*, b.* FROM a, b WHERE SUBSTR(a.ab,1,LOCATE(' ',a.ab,LOCATE(' ',a.ab)+1)) LIKE SUBSTR(b.ab,1,LOCATE(' ',b.ab,LOCATE(' ',b.ab)+1))  AND  SUBSTR(a.ab,1,LOCATE(' ',a.ab,LOCATE(' ',a.ab)+1)) NOT LIKE '' AND SUBSTR(b.ab,1,LOCATE(' ',b.ab,LOCATE(' ',b.ab)+1)) NOT LIKE '';   Dec 15 '11 #4

 P: 48 Thanks Johny. it worked just fine. Dec 15 '11 #5