Need similiar patindex query in MS Access

Fairfield
Fairfield used Ask the Experts™
on
I currently have a sql function that I need to create in MS Access either by function or query.  Please help.

USE [pim89]
GO
/****** Object:  UserDefinedFunction [dbo].[get_MN_Format1]    Script Date: 6/8/2017 4:47:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[get_MN_Format1](
        @pStr   nvarchar(4000)
) RETURNS nvarchar(4000)
AS
BEGIN
 
DECLARE   @pos int, @sFormat  nvarchar(4000) , @candidate nvarchar(4000)
SET @pos = 1 ---Init variable to enter the loop
SET @sFormat = '' --Init return variable  
 
WHILE @pos>0
BEGIN  
        SET @pos = PATINDEX ( '%[^0-9a-Z]%', @pStr )---Any character not number 0-9 or letter a-Z
        SET @candidate = CASE WHEN @pos<=0
                  THEN isnull(nullif(CAST(LEN(@pStr) AS NVARCHAR(4)),'0'),'')
                  ELSE isnull(nullif(CAST(@pos-1 AS NVARCHAR(4)),'0'),'')+SUBSTRING(@pStr, @pos, 1) END
        SET @sFormat = @sFormat+ @candidate
        SET @pStr= SUBSTRING(@pStr  , @pos+1 , LEN(@pStr)) ---cut to get the next string until no more format char
END --END LOOP
---optional not to shows the zeros -replace them with empty, I like it better
        RETURN @sFormat;

END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Can you give a small sample of the data because at first look it iooks it needs some work....

Author

Commented:
Here is some of the data, and what I would anticipate seeing

PN                                           Format

12345-001                              5-3
12345555-002                        8-3
123-12-2345                           3-2-4
12A-3BBB                               3-4
John TsioumprisSoftware & Systems Engineer

Commented:
You should use the Split command along with a simple counter....at least in your example the delimiter is constant "-"...
so you could use something like this
Dim Output as String
Output = ""
NoOfDelimiters =len(Trim([PN]))-len(replace(Trim([PN]),"-",""))
For i = 0 to NoOfDelimiters 
Output = Output & "-" & Len(Split([PN],"-")(i)) 
Next
Output =Left(Output,Len(Output)-1)

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
There are times where the part number could look like this.

12345_444
1122_33556-444

So, I can't look for just the delimiter of "-"
John TsioumprisSoftware & Systems Engineer

Commented:
Do you have a lot of mixed delimiters....?...if not you could modify the above code and include all the delimiters....

Author

Commented:
Yes, there could be lots of delimiters.  I was hoping there was something similar to the sql function in MS access that would find any format.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial