473,320 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to split a text field into words

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.  

4 5415
johny10151981
1,059 1GB
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
almaroc
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
johny10151981
1,059 1GB
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
almaroc
48
Thanks Johny. it worked just fine.
Dec 15 '11 #5

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

Similar topics

2
by: Tim | last post by:
Hi I want to be able to split the contents of a text field into two or maybe three columns. The text field contains text AND HTML mark-up. My initial thought was to find the middle character...
6
by: dixie | last post by:
I have a text field on a form which has names with a comma between them like this: 'J. Smith, A. Jones, A. Man, J. Johns'. I am trying to find a procedure that will count the number of people in...
1
by: Greg Teets | last post by:
I have a field in an access 2000 database that is actually three lines of text separated by carriage returns. Is there a function in the Access version of SQL that will let me locate the...
2
by: Shelley | last post by:
Hi everyone, I am searching a way to read part of the data in the text field in a form. As the length of the field is dynamic, I can't use the left or right function. I need to read the first 2...
0
by: Hugo Coolens | last post by:
I want to order a text field which contains some words which begin with a single quote, if however I perform the following query, the single quote seems to be neglected: select arabic, dutch...
2
by: alexsg | last post by:
I'm setting up a resolutions database where each resolution will be copied from Word documents and pasted into a memo field. The resolution will be in the form: Resolution title <cr> Project no...
3
by: prabodhtiwari | last post by:
function submitPartsForm(str) { var count=document.getElementsByName("partId"); for(var i=0;i<count.length;i++) { document.mylist.myNum.value= document.getElementsByName("partNum")...
2
by: mckurban | last post by:
Hi All, I'm not very familiar with Javascript and need help with setting up some javascript code to allow users to create dynamic radio buttons from text field and then to use selected radio value...
5
by: HY26 | last post by:
Hello - First off, I'm fairly new with using Access. I have a text field that contains a full address and I would like to split that field into 4 parts - street address, town, state, & zip. I...
3
by: Jonas Peter | last post by:
Hi guys, I have got a question that I would love for you guys to give me an idea on how to get started with. First of all, I'm using Windows 7 and Python 2.7 I've got a text file and im...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.