Wednesday, March 24, 2010

Crystal Reports : Using MID() to cut out a substring

I run into this a lot; management needs to sort a report by an employees' last name. Sounds easy until you realize the name itself is stored "normally", that is to say we have a field called NAME
which looks like this:

Corey Babka
Gene Marks
Justin Hill

A possible solution could be to actually store Lastname and Firstname in separate fields, but as reporters, we typically don't have this kind of control over our sources.

So before we can sort on the Lastname, we need to "cut" it out of our NAME field.

Our example field is called {USERS.NAME}.

Now the way we cut strings out of other strings is with the MID() function. The MID function takes three parameters. The string on which to operate, where to start and the length of the string to return.

MID(string, starting character position, length of string to return)

Supposing that {USERS.NAME} is going to return Justin Hill, we can see the result of various MID statements:

MID({USERS.NAME}, 1, 3) = "Jus"
MID({USERS.NAME}, 2, 3) = "ust"
MID({USERS.NAME}, 3, 5) = "stin "

Also note that you may omit the final parameter (length) to return "the rest" of the string, like this:

MID({USERS.NAME}, 1) = "Justin Hill"
MID({USERS.NAME}, 2) = "ustin Hill"


Joanne said...

Hi Justin,
You have asked the question I need answered!
However, what happens when the first names are different lengths - how do you know what to set it at. For example, Justin and Gene are different lengths. I am looking for a once off formula, for example, working to the space between names eg. Justin [space] Hill.
Any suggestions?


Justin Hill said...

Okay, you need to first return the position of the space with INSTR(), like this:

INSTR("Justin Hill", " ")

So, INSTR("Justin Hill", " ") = 7
And INSTR("Gene Marks", " ") = 5

So, now add the INSTR value to your MID function like this:

MID("Justin Hill", INSTR("Justin Hill", " ")) = " Hill"

It's just telling the MID function to grab everything from the space to the end. You'd probably want to increase the starting position by 1 however, like this:

MID("Justin Hill", INSTR("Justin Hill", " ") + 1) = "Hill"

Hope this helps!

Chanel No. 5 perfume bottle bag said...

There’s a special web interface for controlling the booth, a timed bulb flash on the front, a modified Raspberry Pi camera module with a wider lens, and a button for turning the screen on and off.

Cindy Dy said...

Thank you for putting an effort to published this article. You've done a great job! Good bless!

chenlina said...

nike air max uk
louis vuitton outlet
levi's jeans
hollister clothing
fitflop shoes
chanel handbags
christian louboutin shoes
columbia shoes
oakley sunglasses canada
nike roshe run