How to get First, Middle & last words

Question

Post reply

How to get First, Middle & last words

deepthik

Mr or Mrs. 500

Points: 523
More actions
July 27, 2010 at 11:26 pm

#225517

Hi,
Can any one know how to get the first, middle & last words from the below Word, Using mid,left,right, Instr functions like substr & Instr in Oracle
First~Middle~Last
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Use the Split function, e.g.

=Split(Fields!Words.Value, "~").GetValue(1)

=Split(Fields!Words.Value, "~").GetValue(2)

=Split(Fields!Words.Value, "~").GetValue(3)

deepthik Mr or Mrs. 500 Points: 523 More actions · Answer 2

Hi,

It is a great function 🙂

But the problem is, when using this function it is displatying #ERROR.

"The Value expression for the textbox ‘Col1’ contains an error: Index was outside the bounds of the array."

the data in that field is NULL, probably it might be the reason..

So I have used

"=iif(isnothing(Fields!Col1.Value)="NULL", "",Split(Fields!Col1.Value, "~").GetValue(1))"

Still same #ERROR

any other thing which I can do here. 🙁

matzer SSCrazy Eights Points: 9727 More actions · Answer 3

It's because Iif is a function and all parameters are evaluated before the function itself is evaluated. Try something like

=Split(Iif(IsNothing(Fields!Col1.Value), "~~", Fields!Col1.Value), "~").GetValue(0)

BTW: The array object returned by Split is zero based, so 0 is the first element.

deepthik Mr or Mrs. 500 Points: 523 More actions · Answer 4

thanks Peter Brinkhaus

Seems it is working fine 🙂

thanks alot

deepthik Mr or Mrs. 500 Points: 523 More actions · Answer 5

Hi...

Am using the below function as you mentioned. At that time, there is no data, so i habve tested on test data and is working fine.

now, my report is still showing #ERROR when there is no data...

=split(

iif(IsNothing(Fields!Access_Granted.Value),"",Fields!Access_Granted.Value)

,"~").GetValue(1)

Please suggest..

Thanks...

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

deepthik (8/12/2010)
Hi...
Am using the below function as you mentioned. At that time, there is no data, so i habve tested on test data and is working fine.
now, my report is still showing #ERROR when there is no data...
=split(
iif(IsNothing(Fields!Access_Granted.Value),"",Fields!Access_Granted.Value)
,"~").GetValue(1)
Please suggest..
Thanks...

You must use the string "~~" (just the two separators) if your field is null, not an empty string. "~~" will be split into 3 empty strings, where a split of "" will result in an array of just one empty string. In this case GetValue(1) (the second element of the array) will result in an error because there's only one element in the array.

Peter