October 29, 2012 at 10:22 am
hi,
this is my derived column expression , SUBSTRING([Column 0],FINDSTRING([Column 0],"PAT",1),FINDSTRING([Column 0],"~",1))
for below line
TH*4.2*857463*01**20091015*1045*P**~~IS*7564* PHARMACY~PHA*1234567890~PAT*MA*06*987544****SMITH*JOHN****1234 MAIN ST**SOMEWHERE*MA*54356**19500101*M*01*01*INDIA**BURGER~
my result shoudl should be " PAT*MA*06*987544****SMITH*JOHN****1234 MAIN ST**SOMEWHERE*MA*54356**19500101*M*01*01*INDIA**BURGER"
but i am getting only " PAT*MA*06*987544****SMITH*JOHN****12"
whats reason?
October 29, 2012 at 11:12 am
Because
FINDSTRING([Column 0],"PAT",1)
returns position of the first occurance of "PAT" in your [Column 0], which is for the given sample string is 71.
and
FINDSTRING([Column 0],"~",1)
returns position of the first occurance of "~" in your [Column 0], which is for the given value is 36.
Then SUBSTRING returns the 36 characters of the [Column 0] starting from character 71...
October 29, 2012 at 11:18 am
I don't have SSIS available right now to write a statement, but you should be able to derive it from this T-SQL example:
declare @v-2 varchar(1000)
set @v-2 = 'TH*4.2*857463*01**20091015*1045*P**~~IS*7564* PHARMACY~PHA*1234567890~PAT*MA*06*987544****SMITH*JOHN****1234 MAIN ST**SOMEWHERE*MA*54356**19500101*M*01*01*INDIA**BURGER~'
select SUBSTRING(@v,CHARINDEX('PAT',@v,1),CHARINDEX('~',SUBSTRING(@v,CHARINDEX('PAT',@v,1),LEN(@v)))-1)
I can only try SSIS version:
SUBSTRING([Column 0]
,FINDSTRING([Column 0],"PAT",1)
,SUBSTRING([Column 0],FINDSTRING([Column 0],"PAT",1)),"~",1))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply