July 28, 2011 at 5:41 am
Can somebody help me join 2 tables using wildcards.below is the table
records table
centerno eg.0302-0054A
district table
district code: eg.0302,0302A
district name:eg. ablekuma
I want to join the two tables using a wildcard.so that i will have a full table name like
district name district code center no
as the heading.
July 28, 2011 at 5:44 am
Your phrasing is unclear. What do you mean by join two tables using wildcards?
What have you tried? What is your table ddl? Give us create table statements, sample data, and what you expect the results to look like.
July 28, 2011 at 6:04 am
the LIKE operator can be used in a join, as well as CHARINDEx or PATINDEX to try and find matching criteria.
see how i provided copy-and-pasteable CREATE TABLE and sample data via INSERT statements?
if you do the same, you'll get back prven, testable examples fromt eh volunteers here;
many more people will help you in the future if you follow that behavior with any future posts!
--SET XACT_ABORT ON
--BEGIN TRAN
CREATE TABLE records(
centerno varchar(30) )
insert into records select '0302-0054A'
CREATE TABLE district (
districtcode varchar(30),
districtname varchar(30) )
insert into district
select '0302','ablekuma' union all
select '0302A','bcmflvnb'
select * from district
inner join records
--starts with the same chars
on records.centerno LIKE district.districtcode + '%'
select * from district
inner join records
-- same chars anywhere inside
on records.centerno LIKE '%' + district.districtcode + '%'
select * from district
inner join records
-- same chars anywhere inside
on CHARINDEX(district.districtcode, records.centerno) > 0
Lowell
July 29, 2011 at 4:27 am
Thank you for the help.
What i tried doing was, I have two tables namely (records and districts).The records table contain the following fields
Records
CenterNo
Username
Forenames.etc
The districts table also contain the following fields
Districts
District Name
District Code.
The CenterNo in the Records table and the District Code in the District Code have something in common.
The District Code contain 4 and 5 characters and that constitute the first 4 or 5 letters in the CenterNo.
For example in the District Code we can have 0301 or 0601A.These two numbers will be the first 4 or 5 characters in the CenterNo of the Records Table.For example a centerno in a Record table can be 0301-0097 or 0601A9847.
What am trying to do is to use the District Code in the District table to Join the one in the Centerno using the first 4 or 5 letters in the CenterNo.
Thank you
July 29, 2011 at 5:25 am
dekuwisdom (7/29/2011)
The District Code contain 4 and 5 characters and that constitute the first 4 or 5 letters in the CenterNo.
You don't need a wildcard for that. There are a couple of ways to do this. We'll start with the easiest (but most clunky, performance-wise).
SELECT blah
FROM Records r
INNER JOIN District d
ON LEFT(r.CenterNo,CHARINDEX('-',r.CenterNo,0)-1) = d.DistrictCode;
--This is the easy clunky version. CharIndex identifies the splitting hypen
-- and uses the LEFT() function to pull everything to the left of it.
-- You could also use variations of CHARINDEX() with Substring() or LEN()
--Below is a CTE version that's probably going to give you better performance:
;WITH Records2 AS
(SELECT CenterNo, LEFT(r.CenterNo,CHARINDEX('-',r.CenterNo,0)-1) AS DistrictCode,
Username, Forenames,...N
FROM Records)
SELECT blah
FROM Records2 r
INNER JOIN District d
ON r.DistrictCode = d.DistrictCode;
July 29, 2011 at 7:06 am
Thanks.I got this error message when i tried it
SQL Error 1305: FUNCTION nia.CHARINDEX does not exist
But i tried a different statement and it worked.That is
select * from
records n
join districts d
where d.district_code like substr(n.CENTERNO,1,5) or d.district_code like substr(n.CENTERNO,1,4)
This code worked for me but am still interested with yours.If you can tell me why am getting that error message
July 29, 2011 at 7:13 am
we'd have to see your actual code you executed;
nia.CHARINDEX really implies something you changed.
the CHARINDEX function is builtinto SQL, just like ISNULL...so if you preface it with a schema name ([nia] or [dbo]) it will fail.
Lowell
July 29, 2011 at 7:19 am
dekuwisdom (7/29/2011)
Thanks.I got this error message when i tried itSQL Error 1305: FUNCTION nia.CHARINDEX does not exist
... but am still interested with yours.If you can tell me why am getting that error message
As Lowell said, please post the code you actually ran against your system. CharIndex() is a SQL Server system function, so any failures with it would have been a syntax error.
July 29, 2011 at 7:35 am
this is what i tried executing
SELECT *
FROM nia.nia_records r
INNER JOIN nia.nia_districts d
ON LEFT(r.CenterNo,CHARINDEX('-',r.CenterNo,0)-1) = d.district_Code;
and i got this error message
Error Code: 1305. FUNCTION CHARINDEX does not exist
July 29, 2011 at 7:42 am
dekuwisdom (7/29/2011)
this is what i tried executingSELECT *
FROM nia.nia_records r
INNER JOIN nia.nia_districts d
ON LEFT(r.CenterNo,CHARINDEX('-',r.CenterNo,0)-1) = d.district_Code;
and i got this error message
Error Code: 1305. FUNCTION CHARINDEX does not exist
Have you tried running it again since you fixed the nia.Charindex issue? Because the error message you just posted is not the same error message as the first one you posted. And there is nothing wrong with the code you just posted.
If you have run it and are still having that error, there are bigger issues with your SQL Server. Missing system functions are not a good thing. It's time to call Microsoft and figure out how CharIndex disappeared off your system.
If you could post your CREATE TABLE statements for both the tables and some sample data in an INSERT statement, we could test the code you just posted to verify we don't have problems in our system.
July 29, 2011 at 8:06 am
OK.
CREATE TABLE `records` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`SURNAME` varchar(45) DEFAULT NULL,
`FORNAMES` varchar(45) DEFAULT NULL,
`PREVIOUS` varchar(45) DEFAULT NULL,
`DATEOFBIRTH` varchar(45) DEFAULT NULL,
`GENDER` varchar(45) DEFAULT NULL,
`CENTERNO` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ID`)
)
CREATE TABLE `districts` (
`district_name` varchar(70) DEFAULT NULL,
`district_code` varchar(45) NOT NULL DEFAULT '',
PRIMARY KEY (`district_code`)
)
INSERT INTO `nia`.`nia_districts`
(`district_name`,
`district_code`)
VALUES
(
{asokwa, VARCHAR(45)},
{0302: VARCHAR(10)}
);
INSERT INTO `nia`.`nia_records`
(`ID`,
`SURNAME`,
`FORNAMES`,
`PREVIOUS`,
`DATEOFBIRTH`,
`GENDER`,
`CENTERNO`,
)
VALUES
(
{ID: INT},
{WISDOM: VARCHAR},
{YAW: VARCHAR},
{KWAKU: VARCHAR},
{16/08/1983: VARCHAR},
{MALE: VARCHAR},
{0302-9898B: VARCHAR},
);
July 29, 2011 at 8:20 am
MySQL Syntax?
Lowell
July 29, 2011 at 10:29 am
What is your data source? MySQL? SQL Server? DB2? Oracle?
What is your data destination? MySQL? SQL Server? DB2? Oracle?
EDIT: Are you querying MS Access? What is "nia"? A linked server connection? A SQL Server schema?
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy