Access to SQL conversion

I'm trying to convert an Access Query to SQL. I am using a "Mid Statement" to select a portion of a value:

Mid([ITEM_NUMBER],2,7)

When I try to parse this statement in SQL I always get an error "Does not recognize Mid as a valid procedure."

Is there an SQL statement I could use to accomplish this?the sql server equivalent is the SUBSTRING function

instead of
Mid([ITEM_NUMBER],2,7)

use
substring([ITEM_NUMBER],2,7)

Access query conversion

I have this query:
SELECT Trim(Left([Display Name],InStr([Display Name],",")-1))+Trim(Mid([Display Name],InStr([Display Name],",")+2,1)) AS UserName, dbo_Employee.[Display Name]
FROM dbo_Employee;
Works good in access, but when trying to put it un sql server, it comes up with errors.
I know mid is substring, I just don't know what trim and instr are in mssql.

Any ideas?TRIM->LTRIM and RTRIM
MID->SUBSTRING
INSTR->CHARINDEX

All these functions are documented in SQL Server Books Online.|||Thanks for the quick response. Where can I find SQL Server Books Online?|||http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

It is already installed if you have SQL Server client installed.