Substring depending on Delimiter in the string

user393267 发表于 Dev

snp.it

I am writing a procedure in order to handle a file name in SSIS.

Overview:

I am capturing the file name during a Text file load process in SSIS. I have written a procedure in order to split this file name into different components and return the values in form of Variables which I would be using further down the SSIS package.

Problem 
This file name is of the format @FileName ="FILE_DATE_REF_DATETIME".All I need 
to split this in a way like "FILE" , "DATE". I am able to achieve this by using
SUBSTRING(@Filename,0,CHARINDEX('_',@FileName))
and

Substring(@FileName,CHARINDEX('_',@FileName)+1,CHARINDEX('_',SUBSTRING(@Filename,CHARINDEX('_',@Filename)+1,Len(@Filename)))-1)

But here the major problem is when we get an additional '_' in FILE it completely goes wrong. Can anyone please suggest a way that I split the above file name format into FILE and DATE.

EDIT

Samples of FileNames:

asdfkg_20140710_ets20140710_0525_theds
asdjjf_they_20140710_ets20140710_0525_theds
oiuth_theyb_wgb_20140710_ets20140710_0526_theds

I need to extract anything before the 20140710 and also 20140710.

Igor Borisenko

You can do it using PATINDEX instead of CHARINDEX

select SUBSTRING(@Filename,0,PATINDEX('%[_][0-9]%',@FileName))

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-02-12

我来说两句

0条评论

登录后参与评论

上一篇：quickblox使开发和生产使用相同的令牌

来自分类Dev

Related 相关文章

文章

Substring depending on Delimiter in the string

Substring depending on Delimiter in the string

Conditionally splitting a string on a complex delimiter

Check for a substring at the end of string

修改string.Substring

String.Substring解释

Split String by delimiter position using oracle SQL

splitting a string if a substring has occured

Count number of a substring repetition in a string

C＃string.Substring（）或string.Remove（）

将String.split（delimiter）的返回值放入String []

String.substring（）复制基础char []值

循环内的java string.Substring StringIndexOutOfBoundsException

Delete a substring from a string with a filename in Perl

Pulling variable length substring from middle of string

某些输入时String.Substring（）崩溃

.NET 到 Delphi（string.Substring 函数）

Merge two one dimensional String arrays to a single array with delimiter

C＃中string.Split（）与string.Substring（）的效率？

StringIndexOutOfBoundsException：String.substring（）和String.indexOf（）问题

Build string for Linq WHERE clause depending on multiple checkbox choices

当substring只是换行符时，string.find（substring）函数返回什么？

C＃String.Substring是否等效于StringBuilder？

如何检测String.substring是否复制字符数据

Index and length must refer to a location within the string error in substring

java String类中subSequence和subString方法之间的区别

正确使用SQL String_Split和Substring函数

string.substring在我的字符串上失败

正则表达式匹配 string#substring

为什么 string.Join(string delimiter, IEnumerable collection) 返回只有 1 个值的字符串

Julia将String转换为Char，或将Array {SubString {String}，1}转换为char