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.
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 -
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 '';
-
4 5415
Try this query: -
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
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: -
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,' ')));
-
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 -
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 '';
-
Thanks Johny. it worked just fine.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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")...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |