Extract words after carriage return

[TaylorTot]

I am using Crystal 8.5. I have a memo field that contains three lines of data, below is an example:

Front Range
Fort Collins
Recreation

I would like to split this field by the carriage return.

I was able to grab the first line of data with the following formula:

CONVERT(varchar(253),CONTSUPP.LINKEDDOC)

but cannot figure out how to grab just Fort Collins or just Recreation.

Thanks for your help.

 

[synapsevampire]

CR 8.5 can't use memo fields in a formula, so as you've discovered, you need to do this on the databsae side using a database object, or using a SQL Expression.

Since you didn't state the database type, and the syntax varies, please remember to post the basics.

In SQL Server you'd use the PADINDEX or CHARINDEX functions for this.

Here's an example using SQL Server, note that you'll use a CHR(13) instead of the comma in their examples.

If you need additional help, you'll need to identify the database type.

-k

 

[BParsons]

I'm not sure if the split function is available in 8.5 but if it is you could use something like this...

Split({table.field},chr(10))[2]

The 10 identifies the carriage return and the trailing two in brackets indicates that you want the second piece of data based on being split on the carriage returns. In your case the 2 would pull Fort Collins and a 3 would pull recreation. This also assumes that the data you want is always on the same line.

 

[BParsons]

Sorry, the 10 s/b a 13. 10 is for a line feed.

 

[synapsevampire]

Bparsons: That can't be used in CR 8.5 as MEMO fields aren't allowed in formulas in CR 8.5

Also a chr(13) is a carriage return, not chr(10), Chr(10) is a line feed, although some systems they employed one or both of those.

-k

 

[TaylorTot]

Thank you Bparsons that worked I was able to pull out the second line in the memo.

 

[synapsevampire]

It must not be a CR memo field then, as CR 8.5 doesn't support them in a formula.

-k