473,320 Members | 2,177 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.

Query syntax

Two tables - table A has a column of full names (First MI Last), table
B has 2 columns (First, Last) but no middle (and yes, I'd like to
strangle the idiot who designed it!). Is there a syntax that will
allow a query on something similar to:

A.name like (B.first || % || B.last)?

Everything I try seems to wind up with errors where on operand is not
a string.

--
Will Honea
Nov 12 '05 #1
3 4816
I'm assuming that the columns are CHAR columns, so I've included RTRIM.

CREATE TABLE a (name CHAR(50));
CREATE TABLE b (first CHAR(20), last CHAR(20));

INSERT INTO a VALUES('John Q. Public');

INSERT INTO b VALUES('John', 'Public');

SELECT a.name, 'MATCHES', b.first, b.last
FROM a,b
WHERE LOCATE(RTRIM(b.first||' '), a.name) = 1
AND LOCATE(RTRIM(' '||b.last), a.name) = LENGTH(RTRIM(a.name)) -
LENGTH(RTRIM(b.last))
;

NAME 2 FIRST
LAST
-------------------------------------------------- -------
-----------------------------------------------------------------------
John Q. Public MATCHES John
Public

I'll serve as a defense witness if you do decide to strangle that
original designer.

Fred
frsconsulting.com

Nov 12 '05 #2
On Thu, 26 May 2005 06:48:49 UTC fr**********@gmail.com wrote:
I'm assuming that the columns are CHAR columns, so I've included RTRIM.

CREATE TABLE a (name CHAR(50));
CREATE TABLE b (first CHAR(20), last CHAR(20));

INSERT INTO a VALUES('John Q. Public');

INSERT INTO b VALUES('John', 'Public');

SELECT a.name, 'MATCHES', b.first, b.last
FROM a,b
WHERE LOCATE(RTRIM(b.first||' '), a.name) = 1
AND LOCATE(RTRIM(' '||b.last), a.name) = LENGTH(RTRIM(a.name)) -
LENGTH(RTRIM(b.last))
;

NAME 2 FIRST
LAST
-------------------------------------------------- -------
-----------------------------------------------------------------------
John Q. Public MATCHES John
Public

I'll serve as a defense witness if you do decide to strangle that
original designer.


Thanks, Fred. Needless to say, I've been away from SQL too long - I
really could use a "for Dummies" cheat sheet. There used to be a good
one on line but I've lost the url so that's no help. Now, if I can
just finagle enough info out of the rest of the tables to
differentiate 130 (out of 25k) duplicate A.name and 1470 (of 42k)
duplicate B.first/last entries I may get this usable - but I see some
manual entries in my immediate future. That's what happens when you
volunteer, I guess.

--
Will Honea
Nov 12 '05 #3
Hi Will,

I'm a big fan of a free book by Graeme Birchall called the DB2 UDB
Cookbook. Mr. Birchall has kept the book up to date with the latest
version of DB2, and it's a pretty good read.

http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM

Good luck,

Fred
frsconsulting.com

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: joemyre | last post by:
Hi everyone, What I'm trying to do is take php variables i got from user input, and pass them as the MySQL query terms. $query = "select * from ident where ".$searchtype1."=".$searchterm1."";...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
3
by: Paradigm | last post by:
I want to create a pass through query in Access to get data from a MYSQL table. I need to do this using code so that sertain aspects of the query can be changed. When I look at the SQL version of...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
3
by: KevLow | last post by:
Hi, Hope some kind soul can help me out here.. I'm trying to programmatically modify the column headings of a crosstab query such that it can be dynamic based on user specified period (Month...
6
by: Patrick A | last post by:
All - I have the following in the Field row of a column in a query that runs fine against a local table: MSA: InStr(!!,!) I'm now trying to run the query against a linked DB2 table. I get...
1
by: Crash | last post by:
Hi, ..NET v1.x SP1 VS 2003 SQL Server 2000 SP3 Server 2000, XP, Server 2003 I would like to programmatically execute {possibly many} SQL Server batch scripts. Aka I have many scripts that...
6
by: windandwaves | last post by:
Hi Folk I have a query: SELECT COUNT( `SIS`.`ID` ) c, D FROM `SIS` , `SID` WHERE `SID_ID` = `SID`.`ID` AND `BRO` <> "bot" GROUP BY SID.ID
10
by: Daniel | last post by:
In Microsoft Access I can write a query that includes the criteria: Between Date()-7 And Date() to retrieve the records from a table that fall within the last week. I'm trying to write a...
7
by: bryant | last post by:
Hi all. I am new to ASP and working in Expression Web. The following query displays the information I need in the gridview for a single record. SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM",...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
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...

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.