How to split int between text

HC11112

Registered User.
Local time
Today, 04:44
Joined
May 4, 2015
Messages
13
I have a group of part number doesn't come with a standard format. It is very tricky since some does come with some regular flow but some are not. Therefore i try to take 2 different sample category and hope you guys able to provide me some advice. Preferred method in Access formula as i'm not familiar with VDA.:D

My idea is to split the LPN into 3 column eg. CIVUS (Type), 0.13(SIZE), BR (COLOR)

TRA_LPN TYPE SIZE COLOR
CIVUS-0.13-BR
AVS3.0G-B
AVSSH-2.0F-G

TRA_LPN
AGP0219-2.5-GY-O
FLRYY-4X0.75-B
V-R2-0.75-W
V-R2-0.75-Y
 
tricky. I don't think there is an easy answer with such a wide range of formats. I suggest you need to investigate using 'regular expressions' (regex for short) although I suspect even that won't be up to the job. Wildcards are a simple form of regex which can easily be used in a query - see this link

https://support.office.com/en-us/ar...eference-af00c501-7972-40ee-8889-e18abaad12d1

Depending on the volumes if you start by grouping the data by pattern and then splitting out each you will be left with a rump to deal with manually

so for example using a query similar to this will find the same pattern

Code:
select LPN
FROM myTable
WHERE LPN like "*[-]*[-]*"
will pick up
CIVUS-0.13-BR
AVSSH-2.0F-G
FLRYY-4X0.75-B

whilst
Code:
select LPN
FROM myTable
WHERE LPN like "*[-]*[-]*[-]*"

will pick up
V-R2-0.75-W
V-R2-0.75-Y

then you would need to use the split function (vba) or instr/left/mid/right string functions (in a query) to split into the various elements.

but I don't envy you your task:D
 
Thanks for your prompt advice...yes...it indeed tricky and headache since it doesn't come with a standard flow...wondering whether is there anyway to split the int out from the text character?
 
Look for any pattern and work from that. Then deal with those not matching a pattern.

There are not always 2 "-" in the string
The length of the string and its component parts is not constant
There is always a "." in the string???

Do you have a list of such rules?
 
If you split each string on "-" using the function SPLIT, then you check which of the substrings contains a decimal ".", and that is the one to process ... depending on how many combinations there are, and provided these rules cover all the cases.
 
Thanks everyone....so far i have nearly 43k line items which come with different scenario however basically i have breakdown them into into Cat With Space, Cat with - & Cat without space.

They might have different combination however what i can see the majority is, first part mostly text and in the middle will combined with integer and the third part mix with text character again. (closed to 80%).

If able to find a access formula to split the integer between both text will be better. I'm currently using instr, mid to find position first in order to split into type, size and color.
 
you can use this function to do your basic splits - you need to put it in a module, then call it from your query

Code:
 Public Function Splits(source as string, delimiter as string, element as integer) as string
 dim sarr() as string
  
     sarr=split(source,delimiter)
     splits=sarr(element)
  
 end function
and to use it in your query

Code:
 SELECT LPN, splits(lpn,"-",0) as LPNType, splits(lpn,"-",1) as LPNSize, splits(lpn,"-",2) AS LPNColor
 FROM myTable
 WHERE LPN like "*[-]*[-]*"
 
It shouldn't be that bad, I can think of a couple of ways but how many Types are there? List them out if there are not that many. By Types I mean just the CIVUS, AVSSH, V-R2 parts.
 
when you come to do your split, avoid using type as a field name, it is a reserved word and using it can cause unexpected problems
 

Users who are viewing this thread

Back
Top Bottom