Extract part of a string 1

[shelj]

I am using Crystal Reprt 6.0 and I connect to the data with ODBC. There is a description column of varying length that I need to extract. I need the string to the right of the comma that is in the string.
Eg. BALL VALVE, VBA0151-RF
I need to extract VBA0151-RF and place it in another column.
This is just one example, the other strings are different.
If anyone has any ideas, could you please let me know. I tried using the Mid function, but I couldn't get it to work.
This is what my SQL statement looks like:
SELECT
VALVFL.`LONG_DESCR`
FROM
`VALVFL` VALVFL
WHERE
Mid("{VALVFL.`LONG_DESCR`}", 18)

Thank you

 

[kenhamady]

See the InStr() function in the Crystal help. This allows you to calculate the position of any character in the string. You can use that to do a substring:


{LongDescr}
[ Instr ({LongDescr}, ',' ) + 1
to Length ({LongDescr}) ] Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[dcm]

shelj: The problem with your Mid formula is that you were hard coding the start position (18). As Ken Hamady suggests you could use Instr() to locate the position of the first comma, increment this by one and you've got the start of the remaining text. So if you rework your formula as follows:
SELECT
VALVFL.`LONG_DESCR`
FROM
`VALVFL` VALVFL
WHERE
Mid("{VALVFL.`LONG_DESCR`}", Instr("{VALVFL.`LONG_DESCR`}",',')+1)

this should work
David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner