sql query with variables

[paola_bruccoleri]

Hello

I need to do a sql query where I might have to do a script and use variables. I use ADS server 11.1.

I have this table LOG:
FECHA Date
CONFIG Character 100
SUCURSAL numeric 3
Example:
15/11/16 \\terminal-server\programas\config10.dbf 10
15/11/16 \\terminal-server\programas\config4.dbf 6


I need to locate the records whose SUCURSAL number is not in the CONFIG field. For example the record 2. The record one is right because the string .....config10.dbf contains the number 10.
how can I do it?
very thanks

[joachim_drr]

So, change it to:

select * from log where LOCATE( RTRIM(CONVERT(sucursal, SQL_VARCHAR )), config ) = 0

But - hey, what about a record

15/11/16 \\terminal-server\programas\config40.dbf 4

This will return true - in this modified query aswell as in yours, because the CONVERT returns '4' which the locate finds in '...config40...'

[paola_bruccoleri]

Hello

After much testing and searching, here I share the solution.

DECLARE @suc string;
DECLARE cursor1 CURSOR AS SELECT * FROM log;
CREATE TABLE #Temp1 ( fecha Date, hora char(10), usuario char(20), opcion char(50) );
OPEN cursor1;

WHILE FETCH cursor1 DO
@suc = RTRIM(CONVERT( cursor1.sucursal, SQL_VARCHAR ));
/* LOCATE Return integer location (1-based) of str1 in str2, with optional start starting point.
If str1 is not found in str2, 0 is returned.
*/
IF LOCATE( @suc, cursor1.config ) = 0 THEN
INSERT INTO #Temp1 VALUES( cursor1.fecha, cursor1.hora, cursor1.usuario, cursor1.opcion );
END IF;
END WHILE;
CLOSE cursor1;
select * from #Temp1;

It's works right.

The CONTAINS function does not work as expected

With the solution of Joachim I do not get the expected results. Thank you very much for your contribution.

Very thanks

[joachim_drr]

select * from log where not contains(config, 'config'+trim(cast(sucursal as sql_char)))