Using the 'Split' function in an SQL Expression Field 1

[TomSalvato]

Hello all,

I have a data field (string) that looks like this:
11-aa-333-44-555-6666

While the components of the string can vary in size, I ALWAYS want the 2nd component of the string. 'aa' in this case.

In standard formulas, I can just use the split function to parse it out:

split({table2.field},"-")[2]

But I'm trying it in an SQL expression field, and either Crystal or Oracle doesn't seem to like it. Here's how I'm trying it ...


(
select max("table1"."DESCRIPTION")
from "db"."table1"
where "table1"."VALUE" = split("table2"."field","-")[2]
)

Any ideas would be appreciated.

-TS
using CR2008

 

[lbass]

Try something like this to replace the split():

{fn left(
{fn substring("table2"."field",{fn locate('-',"table2"."field")}+1)},
{fn locate('-',"table2"."field")}-1)}

-LB

 

[TeyBrady]

If you are in the SQL Expression it is looking at Oracle syntax. Split is not an oracle function. If you are looking for the characters between the 1st and 2nd dash in a string this type code will work.

select substr('11-aa-333-44-555-6666',
instr('11-aa-333-44-555-6666', '-', 1, 1) + 1,
instr('11-aa-333-44-555-6666', '-', 1, 2) - instr('11-aa-333-44-555-6666', '-', 1, 1) - 1)
from dual

The SQL expression would look like this.

substr(tbl.field, instr(tbl.field, '-', 1, 1) + 1, instr(tbl.field, '-', 1, 2) - instr(tbl.field, '-', 1, 1) - 1)

I'm not sure I understood exactly what you were trying to do.

 

[TomSalvato]

Thanx, Tey!

That's exactly what I needed. Works like a charm.

SQL expression fields always seem to get me hung up, probably the Oracle syntax.

Thanx again!

-TS