Select statement

[CR4Reid]

Hello all,

Using Crystal XI and Oracle ODBC.

I'm trying to extract the first 4 or 5 digits from a field and declare it as the field "OBJECT" in the SELECT statement. The contents of "FIELD_A" is as followed:

S510#CCBB
S1313#BTCC
14444#SQQQ
7980#TUVV

The result that I'm looking for:

S510
S1313
14444
7980

I tried using CHARINDEX and LEN but Crystal won't accept it. Any help is appreciated.

Thanks!

 

[AndyMc]

To get the characters before the '#' sign,

if instr({FIELD_A},"#") > 1 then
left({FIELD_A},instr({FIELD_A},"#")-1)
else {FIELD_A}

 

[CR4Reid]

Thanks for your prompt reply andymc. I neglected to mention that I'm creating a Command object and require the SQL code in the SELECT statement. The current SQL command I have is:

SELECT
SUBSTR("FIELD_A",1,4) AS OBJECT
FROM "DATABASE"

The problem with this statement is it does not show any 5 digit objects. Using my example, it will only show objects S510 and 7980. If I do this:

SUBSTR("FIELD_A",1,5) AS OBJECT

then only the 5 digit objects show up.

 

[CR4Reid]

I answered my own question with your help:

SELECT
SUBSTR("FIELD_A",1,[highlight #FCE94F]instr("FIELD_A",'#',1,1)-1)[/highlight] AS OBJECT
FROM "DATABASE"

Thanks andymc!

 

[AndyMc]

Cool, I'm glad SQL had an InString function also.