Link to home
Start Free TrialLog in
Avatar of Fairfield
FairfieldFlag for United States of America

asked on

Need similiar patindex query in MS Access

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
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

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

ASKER

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
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

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 "-"
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I feel like M.J.Fox from Back to the Future ... :)