I want to do

select *
from [blah], [blah1]
where blah.x = Mid(Blah1,3)

so it would match all of fieldx to fieldy , but ignoring the first three chars in fieldy..

Make sense??

David

 

hmmm... no.

far too technical for me.

 

You can do it using an inner join which is most efficient as it'll use SQL's join strategy for query execution which should optimise more...

SELECT Table1.*, table2.* from table1 INNER JOIN table2 on mid([table1].[a],4) = mid([table2].[a],4)


Or just plain ol' select:
SELECT Table1.id, Table2.id
FROM Table1, Table2
WHERE (((Mid([table2].[a],4))=Mid([table1].[a],4)));

 

MID is not a recognised database function or summit similar...

Ill give you a ring tomorrow.

David

 

aah poo, it just worked for me prototyping in access. Give me a mo.

 

Microsoft SQL Server supports the SQL 99 substring function...

SUBSTRING(extraction_string [FROM starting_position] [FOR length])

Try that.

 

extraction_string can be the column name, too.

 

So should this not work??

as it doesnt...

CREATE VIEW dbo.Test
AS
SELECT Computer_System_DATA.MachineID,
Computer_System_DATA.InstanceKey,
Computer_System_DATA.RevisionID,
Computer_System_DATA.AgentID,
Computer_System_DATA.TimeKey,
Computer_System_DATA.CurrentTimeZone0,
Computer_System_DATA.Description0,
Computer_System_DATA.Domain0,
Computer_System_DATA.Model0,
Computer_System_DATA.Name0,
Computer_System_DATA.Roles0,
Computer_System_DATA.Status0,
Computer_System_DATA.UserName0,
Computer_System_DATA.Manufacturer00,
Computer_System_DATA.SystemType00, PCDetails.TagNo,
PCDetails.Department, PCDetails.Floor, PCDetails.House,
PCDetails.Opsys, PCDetails.Make, PCDetails.Processor,
PCDetails.HDSize, PCDetails.Memory, PCDetails.EquipType,
PCDetails.Model, PCDetails.SerialNumber,
PCDetails.TCPIPAddress, PCDetails.NetworkCard,
PCDetails.Comments, PCDetails.PurchaceDate,
PCDetails.DockingStation, PCDetails.LaptopUser,
PCDetails.LaptopManager
FROM Computer_System_DATA INNER JOIN
PCDetails ON
Computer_System_DATA.Name0 = SubString(PCDetails.TagNo,3,Len(PCdetails.TagNo))


Syntax runs ok but no data returned.

Its too early...

David

 

David,

you're starting from position *4* aren't you? Cant' recall if this is zero indexed or not, but try that. Or first try selecting the substring and take it from there.

 

finally this works...

CREATE VIEW dbo.Test
AS
SELECT Computer_System_DATA.MachineID,
Computer_System_DATA.InstanceKey,
Computer_System_DATA.RevisionID,
Computer_System_DATA.AgentID,
Computer_System_DATA.TimeKey,
Computer_System_DATA.CurrentTimeZone0,
Computer_System_DATA.Description0,
Computer_System_DATA.Domain0,
Computer_System_DATA.Model0,
Computer_System_DATA.Name0,
Computer_System_DATA.Roles0,
Computer_System_DATA.Status0,
Computer_System_DATA.UserName0,
Computer_System_DATA.Manufacturer00,
Computer_System_DATA.SystemType00, PCDetails.TagNo,
PCDetails.Department, PCDetails.Floor, PCDetails.House,
PCDetails.Opsys, PCDetails.Make, PCDetails.Processor,
PCDetails.HDSize, PCDetails.Memory, PCDetails.EquipType,
PCDetails.Model, PCDetails.SerialNumber,
PCDetails.TCPIPAddress, PCDetails.NetworkCard,
PCDetails.Comments, PCDetails.PurchaceDate,
PCDetails.DockingStation, PCDetails.LaptopUser,
PCDetails.LaptopManager
FROM Computer_System_DATA INNER JOIN
PCDetails ON
substring(Computer_System_Data.Name0,4,(len(Comput er_System_Data.Name0)-3)) = pcdetails.tagno


just realised though that as one database holds the site location in different field and the other is the three chars that we are removing.... so told them to sort there db out.. by exporting ours... but proved that this can be done... or will when I tell them!

I owe you a pint nick.

David

 

No worries mate, we got there in the end