Skip to Content
avatar image
Former Member

How to use wildcard in where clause CE Function

Hi all,

    How to use

1. concatenate,shift,sub string in CE Function

2.  wildcard in where clause in CE Function .

It will be helpful if you provide us some examples

Thanks in advance

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 19, 2014 at 10:38 AM

    Hi Mano,

    The table structure i created for the following examples :

    create table TAB1(col1 varchar(20),col2 varchar(20),col3 integer)

    1) Concatenate:

         Use CE_CALC() function in your projection list.

              var1=ce_column_table("TAB1",["COL1","COL2"]);

              var2=ce_projection(:var1,["COL1","COL2",CE_CALC('"COL1" + "COL2"',varchar(40)) AS                                         "COL4"]);

    2) shift

         By shift do you mean to shift the chars of your string one to the left? you can achieve this by using the SUBSTR function.

              charVar:=substr('someText',2,length('someText'));

              select :charVar from dummy;

    3) SUBSTR already shown in the above example

    4) I always use a workaround of using INSTR function to replace the like operator.

    Instr function will return zero if the supplied string is not present in the parent string. hence in a way it can act as a like operator.

              For eample if you want to check for name like '%A%' , then you can use INSTR(NAME,'A')>0

              Another example with for name like 'A%' can be INSTR(NAME,'A')=1

              Ce implementation can be done with  (for scenario in where condition  is COL1 like '%A%' )

                   var1=ce_column_table("TAB1",["COL1","COL2"]);

                   var2=ce_projection(:var1,["COL1","COL2"],'INSTR("COL1",''A'')>0');

          PS: the qoutes surrounding A in the projection is double single qoutes.

    Regards,

    Lalu George

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Dear George/Krishna,

         Wildcard filter using MATCH() and Concatenate using + operator is working fine ,

      Thanks for your help and spending time

  • Nov 19, 2014 at 10:23 AM

    Hi There,

    For calculations you can use CE_CALC and for applying filters you may want to try with CE_PROJECTION and MATCH

    Regards,

    Krishna Tangudu

    Add comment
    10|10000 characters needed characters exceeded