如何在VB.NET中将字母和数字与字母数字文本分开 [英] How to separate alphabet and number from alphanumeric text in VB.NET
问题描述
我有一个名为pop的ms访问数据库,在表中有3列,第一列名为itemnumber,其中包含字母和数字(例如sdfddf-12454),第二列名为itemalphabet ,第三列名为itemnumeric。 itemalphabet列将是提取的字母表和来自列itemnumber的其他字符(例如,sdfddf-)的目的地,itemnumeric列将是从itemnumber列(例如,12454)中提取的数字的目的地。我有一个名为separate的按钮,它将执行分离代码。
单击按钮分开时的预期输出图示:
| itemnumber | itemalphabet | itemnumeric |
| --------------------------------------- ----
| sdfddf-12454 | sdfddf- | 12454
| asddfs * 7822 | asddfs * | 7822
这是我的代码,用于将所有字母和其他字符复制到名为itemalphabet的列中:
psipopconnection()
psipoprec =新ADODB.Recordset
使用psipoprec
。打开(更新pop set set itemalphabet =([itemno],'( ?:[0-9] + \。?[0-9] * | \。[0-9] +)'),psipopcon,2,3)
.Update( )
结束
这里也是我的代码,用于将所有数字复制到名为itemnumeric的列中:
psipopconnection()
psipoprec =新ADODB.Recordset
使用psipoprec
。打开(更新pop set itemnumeric = ([itemno],'(--\ D)'),psipopcon,2,3)
.Update()
结束
我尝试了什么:
我尝试过以上代码,但没有运气得到我想要的东西。
使用这种技术很容易将数字与字母数字字符分开:你遍历所有字符并检查当前字符是否与[0-9]之类的模式匹配!
似乎你正在使用VB / VBA而不是VB.NET ...
如果我是对的,那么你会遇到麻烦,因为< u> MS Access引擎不支持正则表达式。有几种选择:
1.您可以使用字符串函数 [ ^ ]将字符串拆分成部分,例如:
LEFT() [ ^ ]
INSTR() [ ^ ]
RIGHT() [ ^ ]
2.您可以在MS Access数据库中编写自定义函数(使用VBA),然后在您的查询中使用它。请参阅:
Hack 54。 在访问查询中使用正则表达式 [ ^ ]
MS Access中的正则表达式 [ ^ ]
注意:应该在数据库级别调用和执行引用自定义函数的代码!
但是(!)使用VB.NET要容易得多......
< b>
关于答案的1.部分。以下是如何实现这一目标的方法:
SELECT t.itemnumber, LEFT (t.itemnumber,IIF(InStr( 1 ,t.itemnumber, *, 1 )= 0,InStr( 1 ,t.itemnumber, - , 1 ),InStr( 1 ,t.itemnumber, *, 1 )))作为 itemalphabet,< span class =code-keyword> 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]< /跨度>;
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.
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]!
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]";
这篇关于如何在VB.NET中将字母和数字与字母数字文本分开的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!