I am new to Oracle, its looks to be a completly different world for SQL savy. Have a peculiar problem passing the comma delimited string('F5672898,F5672899,F5672100') from SSRS to Oracle.
I have a table X with a column "case_id" of type char(34 byte) , and values are just 8 alphanumeric characters (F5672898 )rest of the characters are stored as spaces.
I am converting the comma delimited string into a column and passing to in statement to find them in X table
select regexp_substr(:Test,'[^,]+', 1, level) from dual connect by regexp_substr(:Test, '[^,]+', 1, level) is not null
When I pass 'F5672898,F5672899,F5672100' to Test variable above I would get a column as below without any spaces.
Above values are stored as case_id(F5672898 ) with spaces in X table. I am searching the column returned by above returned regular expression as shown below which doesn't return me any results. Below expression return results if I pass values with spaces.
select * from X where case_id in (select regexp_substr(:Test,'[^,]+', 1, level) from dual connect by regexp_substr(:Test, '[^,]+', 1, level) is not null)
I tried trimming the case_id to avoid spaces, it gives me results but takes very long time to load. Could some one help me to suffix spaces to the returned values of the regexp or any other approach to resolve the issue.