我有一个在其他字符数据中间有2个星号的字段。我需要检索星号之间的值。例如,我有DOE*JOHN*L
,我需要JOHN
。
我写了以下代码:
SUBSTRING(TRIM(EMPLOYE_NAME),FINDSTRING(TRIM(EMPLOYE_NAME),"*",1) + 1, (FINDSTRING(TRIM(EMPLOYE_NAME),"*",2) - FINDSTRING(TRIM(EMPLOYE_NAME),"*",1) - 2))
当我尝试运行它时,我得到一条DTS_E_INDUCEDTRANSFORMFAILUREONERROR
消息,但它失败了。
如果我去掉末尾的-2,它可以工作,但我有两个额外的字符。因此,使用上面的示例,我将使用JOHN*L
。
我已经在SSIS包的派生列中找到了这个。它是将定长文件导入SQL Server过程的一部分。
你有什么办法让它工作吗?
发布于 2018-09-19 01:04:08
您可以在SQL中使用CHARINDEX
而不是FINDSTRING
来完成此操作。
SUBSTRING(EMPLOYE_NAME, CHARINDEX('*',EMPLOYE_NAME) + 1, CHARINDEX('*',EMPLOYE_NAME,CHARINDEX('*',EMPLOYE_NAME)))
我用'DOE*JOHN*L'
和'DOE*JOHN'
进行了测试,结果都返回了JOHN
。
编辑:
在创建了一篇关于添加条件拆分的冗长文章之后,我发现了原始Derived Column
表达式的问题。我能够使用下面的表达式来处理带有1个空格、1个星号或2个星号的名称。确保表达式字符串中没有任何换行符或回车符。
LEN(EMPLOYE_NAME) - LEN(REPLACE(EMPLOYE_NAME," ","")) == 1 ? (SUBSTRING(TRIM(EMPLOYE_NAME),FINDSTRING(TRIM(EMPLOYE_NAME)," ",1) + 1,LEN(EMPLOYE_NAME)) ): (FINDSTRING(TRIM(EMPLOYE_NAME),"*",2) > 0? (SUBSTRING(TRIM(EMPLOYE_NAME),FINDSTRING(TRIM(EMPLOYE_NAME),"*",1) + 1,(FINDSTRING(TRIM(EMPLOYE_NAME),"*",2) - FINDSTRING(TRIM(EMPLOYE_NAME),"*",1)) - 1)): (SUBSTRING(TRIM(EMPLOYE_NAME),FINDSTRING(TRIM(EMPLOYE_NAME),"*",1) + 1,(LEN(EMPLOYE_NAME) - FINDSTRING(TRIM(EMPLOYE_NAME),"*",1)))))
https://stackoverflow.com/questions/52387358
复制相似问题