Crystal Reports: Need to extract whole string after preceding text
Need crystal reports formula to extract whole string after preceding text.
Example:
CUSIP 02007GQH9
So I need to extract the whole string "02007GQH9" that comes after the sting "CUSIP". The string I want to exact will always be 9 characters long and there will be other text after it that I don't need. Usually, there is a space after "CUSIP" but there could be times where there is not a space.
ASKER
getting a formula error on "string" in the first line of code.
That must be a Crystal Reports thing?! Sorry, I only know SQL Server part, not other parts.
ASKER
no problem. I probably should not have tagged the question with sql.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you not pull the data from the database using the query above and just display it in Crystal?
You can do it in SQL if you are using a Crystal Command for the data source. Normally reports are built from SQL that Crystal creates from the fields you use on the report. You cannot modify that SQL
SUBSTRING(string, CHARINDEX('CUSIP', string) + 5 +
CASE WHEN SUBSTRING(string, CHARINDEX('CUSIP', string) + 5, 1) = SPACE(1)
THEN 1 ELSE 0 END, 9) AS extracted_string
FROM ( SELECT 'CUSIP 02007GQH9' AS string UNION ALL SELECT 'CUSIP02007GQH9' ) AS test_data