Fairfield
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(@pS tr) AS NVARCHAR(4)),'0'),'')
ELSE isnull(nullif(CAST(@pos-1 AS NVARCHAR(4)),'0'),'')+SUBS TRING(@pSt r, @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
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(@pS
ELSE isnull(nullif(CAST(@pos-1 AS NVARCHAR(4)),'0'),'')+SUBS
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
Can you give a small sample of the data because at first look it iooks it needs some work....
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
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
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)
ASKER
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 "-"
12345_444
1122_33556-444
So, I can't look for just the delimiter of "-"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ... :)