InStr equivalent 2

[markSaunders]

Hi,

I currently have a process that involves
[ul][li]importing from Excel to Access[/li]
[li]run several queries to reformat the imported table into a structured db format (e.g. x=category x.x=topic x.x.x=subtopic)[/li]
[li]run a DTS package to copy from Access to SQL[/li][/ul]
The main process involved in the queries is parsing the ID field according to the number of points it contains (see bold above)

I've included examples of the main queries
appending to the category:

INSERT INTO Tbl_Category ( Id, Description )
SELECT CLng([ClassId]) AS NewId, Tbl_import.ClassDesc
FROM Tbl_import
WHERE (((InStr([ClassId],"."))=0));

appending to the topic

INSERT INTO Tbl_Topic ( Description )
SELECT DISTINCT Tbl_Import.ClassDesc
FROM Tbl_Import
WHERE (((IsNumeric([classId]))=True) AND ((InStr([ClassId],"."))>0))
ORDER BY Tbl_Import.ClassDesc;

appending to the subtopic

INSERT INTO Tbl_SubTopic ( Description )
SELECT DISTINCT Tbl_Import.ClassDesc
FROM Tbl_Import
WHERE (((IsNumeric([classId]))=False))
ORDER BY Tbl_Import.ClassDesc;

Mark Saunders

 

[bperry]

Hi Mark,
I'm not familiar with InStr(), but I gather it is used to count how many times a period (.) occurs in a particular string??

If that's correct, then there's no direct equivalent of InStr() in SQL Server.

However, if you're using SQL 2000, then I have a UDF that essentailly does the same thing as your InStr(). You can see an example of how it works (and copy/paste the code if you want) at the link shown below.

http://www3.ns.sympatico.ca/brianperry/sqlnotes/udf.html

rgrds, etc
brian perry

 

[markSaunders]

I've looked at the UDF and it looks the part.

Will have to leave as is for now, but will most cerainly be setting a lunch time project of getting it to work with the UDF (or similar)!

cheers - and a helpful site too!

m Mark Saunders

 

[markSaunders]

bperry - oops! just so you know - InStr() returns the location of the instance of the character being searched for. It has parameters that allow the start location etc. for this particular scenario the UDF is actually more simple that having to use to InStr to search for each dot - (relative to the location of the previous dot)...

!o) Mark Saunders

 

[MarkSweetland]

Instr (VB/VBA) returns the index of the occurance of a search pattern in another string;

InStr(start, Str1, str2)

CharIndex (T-SQL) returns the same information but the arguments are in a different order;

CHARINDEX (expression1, expression2 [ , Start_location ])

Mark

 

[markSaunders]

cheers mark. that's really useful too - may even be able to virtually cut and paste the current access queries into sql now!

m Mark Saunders