How to separate alphabet and number from alphanumeric text in VB.NET

Question

1.00/5 (1 vote)

See more:

VS2010

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 15-Mar-19 0:42am

kyrons

Updated 15-Mar-19 1:24am

Add a Solution

Comments

Leo Chapiro 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.

2 solutions

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

Posted 15-Mar-19 0:50am

Leo Chapiro

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2019-03-15T01:24:00

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:

SQL

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]";

Posted 15-Mar-19 1:24am

Maciej Los

Updated 15-Mar-19 3:59am

v2

Comments

Leo Chapiro 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.

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