grouping by partial string

Hello All,

I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...

Bob001

Bob

Robert005

Doug053

Doug100

Douglas

Barney001

Frank

I want to group it up as...

Bob

Doug

Barney

Frank

And then be able to summarize the results in the matrix.

Thanks in advance for any help

-Clint

If the last 3 characters are always 3 digits then you can simply group by left(Name, len(Name) - 3)

Adamus

|||

Just be sure your SELECT left(Name, len(Name) - 3) contains the trim.

Adamus

|||Unfortunately they do not all end in 3 digits. Ill fix my example. thanks though|||

Ok next question:

If digits exist, will they always be 3 digits on the tail end of the name?

If so you can check CASE Name WHEN isnumeric(right(Name, 3)) ...

If not, you'll probably want to use regular expressions to grab the text only portion.

In that case, you'll have to create a UDF which you can find here

Adamus

|||

Thanks Adamus,

Yes, when there are numbers present at the end, there are always three.

What would the expression look like?

Thanks much

Clint

|||

CREATETABLE #temp(myName varchar(20))

INSERTINTO #temp SELECT'Bob001'

INSERTINTO #temp SELECT'Bob'

INSERTINTO #temp SELECT'Robert001'

INSERTINTO #temp SELECT'Robert'

SELECT*FROM

(

SELECTCASEWHENISNUMERIC(RIGHT(myName, 3))= 1

THENLEFT(myName,LEN(myName)- 3)ELSE myName ENDAS [Result]

FROM #temp

)as t

GROUPBY Result

DROPTABLE #temp

Adamus

|||

What happened to Robert in the second list? Was he fired? Did he quit?

On A serious note, someone posted the code you need to remove the digits...

though it does not appear that it will solve the problem as "Douglas" has no digits on the end of it.

What you are looking for, sounds to me is some type of pattern matching...

|||

nice touch.. Mr. Turner...

I hope he actually uses the correct sql to do a select into the temp table instead of doing it all literally.

|||Thanks for the help. I am trying to accomplish in reporting services itself, not in the dbase. From what I gather there is now expression that would do the same thing. Again,I appreciate the help.|||

You can do this in SQL in the DataSet instead of the database or you can do in an expression using the Len function to find out how long the initial string is, the InStr function to find the location of a number, and then the Mid function to extract a string that is the Total Length long minus the Index value of the number that was found. Wrap all this in an IIF to handle the cases when no number is present.

HTH

Dean

|||

After regrouping at another field that seperated out the fields ending in - 083, i was able to get it working with your expression modified. Thanks so much for your help. Here is what ended up working...

Code Snippet

=iif(Fields!BankNumber.Value="083",left(Fields!TestName.Value, len(Fields!TestName.Value) - 11),Fields!TestName.Value)

|||oops. turns out not to work quite right after all. I errors out the first field name but works for the rest. I get a warning saying that the Length statement must be equal to or greater than 0. Since we are using a negative number, it is popping a warning.|||

Exactly. Im trying to figure out how to trim the last 11 characters off certain fields. My real life example has some fields ending in - Bank 083 (or another 3 numbers). i am thinking of the "LIKE" to seach for the pattern and remove it for group sake. Anyone have an idea on that one?

Thanks,

Clint