join using substring

[Bell1991]

Is this possible:

Select * from tblA
INNER JOIN tblB on tblA.numID = tblB.substring(numID, 1, 10)

I am getting an error.

How do i get around this?

 

[gmmastros]

Select * from tblA
INNER JOIN tblB on tblA.numID = substring([!]tblB.[/!]numID, 1, 10)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

your problem is here

Select * from tblA
INNER JOIN tblB on tblA.numID = tblB.substring(numID, 1, 10)

It should be this

Select * from tblA
INNER JOIN tblB on tblA.numID = substring(tblB.numID, 1, 10)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Bell1991]

Thanks

 

[mrdenny]

I would recommend against doing this. As tblB grows this query will take longer and longer as a table scan on the tblB table must happen each time the query runs.

I would recommend adding a column to the table which stored the substring information that you are looking to join against, then index this column join against it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com