TRIM function in SQL expression field

[sambireddy]

In crystal 8.5 there is no trim function for SQL expression fields that I have seen.I saw LTRIM and RTRIM.Any idea how to use TRIM for an SQL expression field for ORACLE database?

 

[BobSuruncle]

Combine the LTRIM and RTRIM into one statement:

{fn RTRIM({fn LTRIM("AP_LOG"."AP_USERID&quot})}

HTH


Bob Suruncle

 

[sambireddy]

Bob,thanks for the solution.When I used LTRIM and RTRIM together, it did work for RTRIM(EX:to trim the spaces in the right) not for LTRIM(Ex:to trim spaces in the left) or TRIM(to trim spaces on left and right).Any solution please?

 

[sambireddy]

The field I am trimming contains data as follows:
" T7350092"
"528250677* "
" 9921215 "
The report has a parameter to input the above values.As the table contains more than 300000 records,I thought I should not use trim in record selection criteria which makes the report to run slow.So I ended up in using the SQL expression field and use that SQL expression field to take the parameter value and pull the data.Please suggest how to trim the database field via SQL expression field.

 

[synapsevampire]

A SQL Expression does not use Crystal syntax, it uses your database's SQL syntax.

LTRIM and RTRIM are old world standards and should work on most databases.

-k

 

[AnnetteH]

Since TRIM is a valid Oracle function, you can use it in a SQL Expression even though it is not listed in the SQL Expression Editor. Just type it in.

 

[sambireddy]

When I typed TRIM it gave me an error "ODBC ERROR:[ORACLE][ODBC]Driver not capable".

 

[synapsevampire]

Annette: I seem to recall that Oracle started with trim() in 8, prior versions required ltrim/rtrim.

-k

 

[sambireddy]

I am using the oracle version 9, when I use trim in TOAD it works

 

[AnnetteH]

Sounds like the problem is with the ODBC driver. It looks like you're using the Oracle ODBC driver. That's a pretty bad idea anyway. Try the CR Oracle ODBC driver that ships with Crystal.

TRIM works fine for me in SQL Expressions using the CR Oracle ODBC driver against Oracle 9i.

 

[lyanch]

Trim also works fine for me using a native connection..

Lisa