David_Wallis

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

suba

hmmm... no.

far too technical for me.

chiark

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)));

David_Wallis

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

Ill give you a ring tomorrow.

David

chiark

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

chiark

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

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

Try that.

chiark

extraction_string can be the column name, too.

David_Wallis

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

chiark

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.

David_Wallis

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

chiark

No worries mate, we got there in the end