Microsoft Access 2010 SQL在"X“处拆分字符串并乘以
Microsoft Access 2010 SQL在"X“处拆分字符串并乘以
提问于 2013-10-11 05:19:52
我有一个包含package size列的表，该表的数据类型为text，出于数学原因，我需要将其转换为整数。此列中的值通常类似于"100ML“、"20GM”、"UD 20“、”13 is“。这里有点棘手，偶尔会有"6X12ML”、"UD 5X6ML“这样的值。其中带有"X“的那些我需要删除我当前正在使用的"ML”
Replace([TABLE_NAME].[COLUMN_NAME],"ML","")在查询的表达式列中。我可以使用嵌套的替换函数来删除"ML“、"GM”、"OZ“和"UD”。我所有的尝试都失败了，我想最终的解决方案应该是这样的
IIf([TABLE_NAME].[COLUMN_NAME] Like "X", (CInt(Left([TABLE_NAME].[COLUMN_NAME],InStr(1,[TABLE_NAME].[COLUMN_NAME],"X")-1))*CInt(Right([TABLE_NAME].[COLUMN_NAME],InStr(1,[TABLE_NAME].[COLUMN_NAME],"X")+1))),[TABLE_NAME].[COLUMN_NAME])我已经尝试使用上述代码的一个变体，但没有效果。感谢所有的建议，我更喜欢在一个查询中删除这个，但我确实意识到我可以使用and表达式，只需将"X“之前和之后的文本拆分为两个不同的表达式列。然后使用另一个查询将这些值相乘。
回答 1
Stack Overflow用户
发布于 2013-10-12 02:00:18
QTY_ORDERED: IIf(InStr(1,Replace(Replace(Replace(Replace([STANDARD_PRICING].[PACKAGE_AMOUNT],"GM",""),"ML",""),"UD","")," ",""),"X")>1,[CRX_HISTORIC_PO].[QUANTITY]/Left(Replace(Replace(Replace(Replace([STANDARD_PRICING].[PACKAGE_AMOUNT],"GM",""),"ML",""),"UD","")," ",""),InStr(1,Replace(Replace(Replace(Replace([STANDARD_PRICING].[PACKAGE_AMOUNT],"GM",""),"ML",""),"UD","")," ",""),"X")-1)*Right(Replace(Replace(Replace(Replace([STANDARD_PRICING].[PACKAGE_AMOUNT],"GM",""),"ML",""),"UD","")," ",""),Len(Replace(Replace(Replace(Replace([STANDARD_PRICING].[PACKAGE_AMOUNT],"GM",""),"ML",""),"UD","")," ",""))-InStr(1,Replace(Replace(Replace(Replace([STANDARD_PRICING].[PACKAGE_AMOUNT],"GM",""),"ML",""),"UD","")," ",""),"X"))*-1,[CRX_HISTORIC_PO].[QUANTITY]/Replace(Replace(Replace(Replace([STANDARD_PRICING].[PACKAGE_AMOUNT],"GM",""),"ML",""),"UD","")," ","")*-1)上面的代码是我用来完成手头任务的代码。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19306426
复制相关文章
相似问题
腾讯云开发者
