SQL Server Performance Forum – Threads Archive

Help with INSTR function

Hello, I have the following query which I actually run in the MS-Access to get the result.But it was not returning any rows , so I am testing in SQL analyser. SELECT dbo_pple_t_Student_Profile.ix_Student_Number AS Expr1,
dbo_pple_t_Person.ix_Last_Name, dbo_pple_t_Person.ix_First_Name,
dbo_rmgt_t_Room_Person.CK_BED_SPACE, dbo_rmgt_t_Room_Person.CK_MOVE_IN_DATE,
dbo_rmgt_t_Room_Person.CHECK_IN, dbo_rmgt_t_Room_Person.ROOM_PERSON_MOVE_OUT_DATE,
dbo_rmgt_t_Room_Person.CHECK_OUT,
Mid([ck_bed_space],1,InStr(1,[ck_bed_space],"-",2)-1)instr AS Building INTO [OccupancyTotals-Chargeback]
FROM dbo_rmgt_t_Room_Person, dbo_pple_t_Person, dbo_pple_t_Student_Profile
WHERE ((dbo_rmgt_t_Room_Person.FK_TERM_ID)="0760")); When I execute this I get the error:
Server: Msg 195, Level 15, State 10, Line 6
‘InStr’ is not a recognized function name. What should I do or change with the syntax.Is there any difference between the queries in access and sql . Thanks

---

In Access, you are allowed to include Visual Basic functions in the Jet SQL syntax. So those are already two big differences with the T-SQL syntax that you can use in SQL Server. Perhaps your Access front-end is retrieving the data from a SQL Server database, but even then the use of the InStr() function means that the Jet database engine (the core of the Access software) will execute the query, instead of delegating it to SQL Server. Instead of InStr(), you could use T_SQL’s own PATINDEX() or CHARINDEX() – and that’s just a start. I would suggest you start learning T-SQL, which should be a smart career move anyway! (Not that there’s anything wrong with Access …)

---

You are even using MID in your query, which is again not a recognised function in T-SQL.
So you even need to replace it with SUBSTRING Syntax.

---

]]>