By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,651 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
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

✓ answered by johny10151981

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.  

Share this Question
Share on Google+
4 Replies


100+
P: 1,059
Try this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT a.*, b.* from tbla a, tblb b WHERE SUBSTRING(a.Addr,0,LOCATE(a.Addr,' ',LOCATE(a.Addr,' ')))
  2.  LIKE SUBSTRING(b.Addr,0,LOCATE(b.Addr,' ',LOCATE(b.Addr,' ')));
  3.  
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
  1. SELECT a.*, b.ESI from datatable1 a, esi_table b WHERE SUBSTRING(a.ADDRESS,0,LOCATE(a.ADDRESS,' ',LOCATE(a.ADDRESS,' ')))
  2.      LIKE SUBSTRING(b.ADDRESS,0,LOCATE(b.ADDRESS,' ',LOCATE(b.ADDRESS,' ')));
  3.  
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
  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.  
Dec 15 '11 #4

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

Post your reply

Sign in to post your reply or Sign up for a free account.