Click here to Skip to main content
14,030,704 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have an ms access database with table named "pop" and inside the table there are 3 columns, the first column named "itemnumber" which contains alphabet and numbers (e.g. sdfddf-12454), the second column named "itemalphabet", and the third column named "itemnumeric". The "itemalphabet" column will be the destination for the extracted alphabets and other characters from column "itemnumber" (e.g.sdfddf-) and the "itemnumeric" column will be the destination for the extracted numbers from column "itemnumber" (e.g.12454). I have 1 button named "separate" that will perform the code for separation.

Expected output illustration when the button separate is clicked:

| itemnumber | itemalphabet | itemnumeric |
|-------------------------------------------
|sdfddf-12454 | sdfddf- | 12454
|asddfs*7822 | asddfs* | 7822

Here is my code for copying all alphabet and other characters into column named "itemalphabet":

psipopconnection()
       psipoprec = New ADODB.Recordset

       With psipoprec
           .Open("update  pop set itemalphabet=([itemno] ,'(?:[0-9]+\.?[0-9]*|\.[0-9]+)')", psipopcon, 2, 3)

           .Update()
       End With


Here is also my code for copying all numbers into column named "itemnumeric":
psipopconnection()
        psipoprec = New ADODB.Recordset

        With psipoprec
            .Open("update  pop set itemnumeric=([itemno], '(-\D)')", psipopcon, 2, 3)

            .Update()
        End With


What I have tried:

I tried those codes above but no luck of getting what I want.
Posted
Updated 15-Mar-19 1:24am
Comments
_duDE 15-Mar-19 10:24am
   
Please check the title of your question: do you really need to separate alphabet and number from alphanumeric text in VB.NET or rather in MS Access?
kyrons 21-Mar-19 4:26am
   
Thanks _dude for clarification. What I really want is that using vb.net project with one (1) button in visual basic form I want to separate alphabet and number from each other.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You need to use .NET Regular Expressions.
It will be easy to separate the digits from alphanumeric characters by using this technique: you loops over all characters and checks whether the current char matches with pattern like [0-9]!
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Seems you're using VB/VBA instead of VB.NET...

If i'm right, than you're in troulble, because MS Access engine does NOT support regular expressions. There's few alternatives:
1. You can use string functions[^] to split string into parts, for example:
LEFT()[^]
INSTR()[^]
RIGHT()[^]

2. You can write custom function in MS Access database (using VBA), then use it in your query. See:
Hack 54. Use Regular Expressions in Access Queries[^]
Regular Expressions in MS Access[^]

Note: a code which refer to custom function should be called and executed on a database level!

But(!) it would be much easier to use VB.NET...

[EDIT]
As to the 1. part of my answer. Here's a way how you can achieve that:
SELECT t.itemnumber, LEFT(t.itemnumber, IIF(InStr(1, t.itemnumber, "*", 1) =0, InStr(1, t.itemnumber, "-", 1), InStr(1, t.itemnumber, "*", 1))) As itemalphabet, RIGHT(t.itemnumber, LEN(t.itemnumber) - IIF(InStr(1, t.itemnumber, "*", 1) =0, InStr(1, t.itemnumber, "-", 1), InStr(1, t.itemnumber, "*", 1))) As itemnumeric
FROM test As t
WHERE t.itemnumber LIKE "*[0-9]";
   
v2
Comments
_duDE 15-Mar-19 8:52am
   
Sorry to say this, Maciej, but VBA does support RegEx, I personly have used it ofter, take a look for example https://stackoverflow.com/questions/3770672/regular-expressions-in-ms-access-vba/3770828
Maciej Los 15-Mar-19 8:57am
   
I didn't say, that VBA does not support Regex. I've stated that MS Access database engine does not support it. A second part of my answer suggests to use VBA macro to split string into parts.
You didn't read my answer carefully... ;(
BTW: your answer is not related to OP question, because OP is using regular expressions within ADODB.Recordset, but - as i stated - MS Access database (even through OleDb provider) does not support them.
_duDE 15-Mar-19 10:13am
   
I want to point to the title of OP's question: "How to separate alphabet and number from alphanumeric text in VB.NET". This question is exactly what I answered :)
Dave Kreskowiak 15-Mar-19 10:36am
   
Actually, VBA does support regular expressions, through the VBScript libraries. You can set a reference to "Microsoft VBScript Regular Expressions 5.5" and you've suddenly added RegEx support to your VBA code.
kyrons 21-Mar-19 4:45am
   
Thanks for your effort Maciej Los. Where exactly to put the code? Is it in Dataset TableAdapter query or just inside the button code: Private Sub Buttonseparate_Click(sender As System.Object, e As System.EventArgs) Handles Buttonseparate.Click

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.190419.4 | Last Updated 15 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100