search just a few caracters

[filipe29]

Hi.How to do with a integer field what i do like strings

select * from venda
where nome like '%'+xxxxx+'%'

nome is a string and i want to do the same with a integer field

 

[swampBoogie]

select * from venda
where cast(integerColumn as varchar(20)) like '%'+xxxxx+'%'

 

[filipe29]

i have now:
select * from cliente
where cast(idcliente as varchar(20)) like '%'+61+'%'

and i receive:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '%' to a column of data type int.

 

[SQLDenis]

select * from cliente
where cast(idcliente as varchar(20)) like '%61%'

or

select * from cliente
where cast(idcliente as varchar(20)) like '%'+convert(varchar,61)+'%'

If the idcliente is already a varchar then this is all you need

select * from venda
where nome like '%'+convert(varchar,61)+'%'


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[filipe29]

yes.I did it but now i need to do that by a parameter on runtime.

 

[SQLDenis]

replace 61 with @var

example
declare @v int -- or make varchar and take out convert in the where clause
select @v =61

select * from venda
where nome like '%'+convert(varchar,@v)+'%'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[filipe29]

Sorry i forgot to mention that when i talk in runtime i must say that i am developing on delphi.

 

[r937]

if idcliente is numeric, and if you go ahead with the LIKE strategy, then you will return idcliente=610, idcliente=161, idcliente=61937, etc.

are you sure you want to do that?

r937.com | rudy.ca

 

[SQLDenis]

make a stored procedure out of it and then it doesn't matter if you use Deplhi, Kylix or Fujitsu COBOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/