Need Loop to work with different "arrays"

[UnsolvedCoding]

I am working on what should be a simple program but am stuck so any help would be appreciated.

As the program runs it comes to a part where it searches a string that is the path to a document.

The way it was written in VBA is it would look for 4 different variations for each deptartment and department number before moving on. Example - D4, D.4, D.04 or Dept4.

The problem is that I don't know how to get this to work in SQL. In VBA I could simply run a loop telling the computer to look for "D" & Nbr_Array(i) or "D."& Nbr_Array(i) etc, but in SQL how do I do that?

 

[gmmastros]

Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnsolvedCoding]

Drive:\Folder\D4\Folder\Folder\Folder\Folder\Folder\Excel_Document_Name\Tab_Name

Result would be 4

The problem is that the department can be located anywhere in the path. In this example it could be listed as D4, D.4, D.04 or Dept4 and could be any one of over 125 different departments.

 

[Qik3Coder]

What do you need to do with it? and do you know the number before hand?

Lodlaiden

You've got questions and source code. We want both!

 

[UnsolvedCoding]

No, the deptarment number in not known before hand. All I know is that it will be a department in the list of existing departments.

All of this eventually gets dumped into an Access database where the department is to be available as a column for the user.

The data is crunched so that parts of the document are broken out for specific information and then the path to the document, the document name, the tab name and starting cell are hooked together into a hyperlink.

This hyperlink in Access allows the user to hover over the link and look at the document name or to click the link and open the document in Excel.

 

[gmmastros]

This number you are looking for....

Will it be the only number in the data?
Will it be the first instance of numbers in the data?




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

This is dirty, but it's a start:

Declare @tbl as table(
col1 varchar(100))

INSERT INTO @tbl SELECT 'Drive:\Folder\D1\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\D.2\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\D.03\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\D04\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\Dept5\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\Dept.6\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\Dept.07\Folder\Folder\Folder\Folder'

select 
CASE WHEN PATINDEX('%D[0-9]%', col1) > 0 THEN 
	substring(col1, PATINDEX('%D[0-9]%', col1)
	+1 --offset for pre department pattern
	, charindex('\', substring(col1, PATINDEX('%D[0-9]%', col1)
	+1 --offset for pre department pattern
	, 10))-1)
WHEN PATINDEX('%D.[0-9]%', col1) > 0 THEN
	substring(col1, PATINDEX('%D.[0-9]%', col1)
	+2 --offset for pre department pattern
	, charindex('\', substring(col1, PATINDEX('%D.[0-9]%', col1)
	+2 --offset for pre department pattern
	, 10))-1)
WHEN PATINDEX('%Dept[0-9]%', col1) > 0 THEN
	substring(col1, PATINDEX('%Dept[0-9]%', col1)
	+4 --offset for pre department pattern
	, charindex('\', substring(col1, PATINDEX('%Dept[0-9]%', col1)
	+4 --offset for pre department pattern
	, 10))-1)
WHEN PATINDEX('%Dept.[0-9]%', col1) > 0 THEN
	substring(col1, PATINDEX('%Dept.[0-9]%', col1)
	+5 --offset for pre department pattern
	, charindex('\', substring(col1, PATINDEX('%Dept.[0-9]%', col1)
	+5 --offset for pre department pattern
	, 10))-1)
ELSE '' END
, col1 
from @tbl

Lodlaiden

You've got questions and source code. We want both!

 

[UnsolvedCoding]

gmmastros

The number can be anywhere in the string, it is not in a set location.


Qik3Coder

Is that the best way when there are over 125 departments with at least 4 variations for each department?

 

[gmmastros]

UnsolvedCoding,

Please take a look at this blog I wrote a while ago:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

In the blog, I show a user defined function that will pick out the first number that appears in a string. This is why I asked if the dept number you are looking for will be the only number (or at least the first number) in the string.

The code I show in the blog will only pick out the first number within the string of data. It is very efficient (performs well) and will likely simplify your query a lot.

Using the same data that Qik3Coder shows, the query would look like this:

Declare @tbl as table(col1 varchar(100))

-- Hard code sample data
INSERT INTO @tbl SELECT 'Drive:\Folder\D1\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\D.2\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\D.03\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\D04\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\Dept5\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\Dept.6\Folder\Folder\Folder\Folder'
INSERT INTO @tbl SELECT 'Drive:\Folder\Dept.07\Folder\Folder\Folder\Folder'

-- The query to get the numbers only
select dbo.GetNumbers(col1) As DeptNumber, Col1
from @tbl

The results of this query are:

[tt]
DeptNumber
----------
1
.2
.03
04
5
.6
.07
[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[drlex]

Perhaps not the 'best way', but surely a good way would be to have a table that contains all variations of all departments and use that in a set-based solution? It is then much easier to maintain.

soi là, soi carré