Solution for Dynamic String search

Script to implement it in Oracle:

Initial step:
SQL> conn sys/password@dba3 as sysdba

Connected.

SQL> GRANT create type TO Demo1;

Grant succeeded.

SQL> GRANT CREATE ANY PROCEDURE to Demo1;

Grant succeeded.

SQL> conn demo1/demo1@dba3;

Connected.create table product ( produt_name varchar2(50), version varchar2(20));

insert into product values('WinZip 6.3','6.3.0');
insert into product values('WinZip 8.0','8.0');
insert into product values('WinZip 8.1','8.1');
insert into product values ('UltraEdit 14.10','14.10');
insert into product values ('UltraEdit 15.0','15.0');
insert into product values ('PL/SQL Developer 5.0','5.0');
insert into product values ('PL/SQL Developer 5.1','5.1');
commit;

SQL> select PRODUT_NAME from product;

PRODUT_NAME --------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Dynamic String search:
Need to identify the list product names based on the dynamic parameter.

1. The first parameter is WinZip,UltraEdit. So the below script is having the two conditions in where clause.

SQL> select PRODUT_NAME from product
2 where PRODUT_NAME like 'WinZip%'
3 or PRODUT_NAME like 'UltraEdit%';

PRODUT_NAME
----------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
2. The first parameter is WinZip,UltraEdit, PL/SQL Developer. So the below script is having the three Conditions in where clause.

SQL> select PRODUT_NAME from product
2 where PRODUT_NAME like 'WinZip%'
3 or PRODUT_NAME like 'UltraEdit%'
4 or PRODUT_NAME like 'PL/SQL Developer%';

PRODUT_NAME
------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Issue: if we need to search 10 products then we need to add 10 Conditions in where clause.

Solution for Dynamic String search:

SQL> create or replace type str2tblType as table of varchar2(30);
2 /
Type created.


SQL> create or replace function str2tbl( p_str in varchar2 ) return str2tblType
2 PIPELINED
3 as
4 l_str long default p_str ',';
5 l_n number;
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
11 l_str := substr( l_str, l_n+1 );
12 end loop;
13 return;
14 end;
15 /

Function created.
Pass the parameter as a CSV.
SQL> variable foo varchar2(50);
SQL> exec :foo := 'WinZip%,UltraEdit%';

PL/SQL procedure successfully completed.
SQL> select product.PRODUT_NAME from product ,
2 (select *
3 from table( cast( str2tbl(:foo) as str2tblType ) )
4 where rownum >= 0) x
5 where product.PRODUT_NAME like x.column_value;


PRODUT_NAME
-------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0



SQL> exec :foo := 'WinZip%,UltraEdit%,PL/SQL Developer%';

PL/SQL procedure successfully completed.

SQL> select product.* from product ,
2 (select *
3 from table( cast( str2tbl(:foo) as str2tblType ) )
4 where rownum >= 0) x
5 where product.PRODUT_NAME like x.column_value;

PRODUT_NAME
------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Script to implement it in SQL Server:


BEGIN

DECLARE @value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint,
@sText varchar(8000),
@sDelim varchar(20)


/* Provide list of software names */
SET @sText = 'WinZip,UltraEdit'
SET @sDelim =','
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1



if (SELECT OBJECT_ID('tempdb..#retArray')) > 0
begin
Drop table #retArray
end

create table #retArray (value char(1000))

WHILE @bcontinue = 1
BEGIN
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT #retArray (value)
VALUES ('%'+@value+'%')
END
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
SET @value = @sText
BEGIN
INSERT #retArray ( value) VALUES ('%'+@value+'%')
END
SET @bcontinue = 0
END
END




select distinct title ProductTitle,
Publisher Vendor,
version Productversion
from Product a , #retArray b
where rtrim(ltrim(a.title )) like rtrim(ltrim(b.value))
order by title
END