Converting a Wildcard in to a number

[PETEHO]

use RC001
declare @line_amount integer
set @line_amount = null
select top 10 * from rec_items where
(ABS(line_amount) like convert(int,isnull(@line_amount,'%')))
and rec_id = 'HWK1004'


The column itself only contains numbers and is always populated however im using the code to build a report in SSIS where there are several other selectable columns and want the user to be able to leave the column blank. As the column always contains values I want to replace the blnak column which will accept NULLS thotugh the SSIS design to replace those nulls we a wild card. The problme is I need to affective convert the wildcard into a number.

Thanks for your assistance

 

[SQLBill]

Would you please show examples of what you have and what you want? Your request is very confusing.

You say the column always contains values. But then you say you want to replace the blank column with a wild card. How can the column be blank if there are always values in it?

I think you want to enter the % symbol into a integer column. You won't be able to do that since % isn't an integer. You would have to make the column an alphanumeric column, such as varchar, char, nvarchar, nchar, etc.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

Not sure if this is what you want, but here you go:

use RC001
declare @line_amount integer
set @line_amount = null
select top 10 * from rec_items 
where (ABS(line_amount) LIKE @line_amount) OR @line_amount IS NULL)
and rec_id = 'HWK1004'

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

You can't apply LIKE to ints.

LEt's see what nonsense your expression is doing:

convert(int,isnull(@line_amount,'%')))

Since you define @line_amount to be NULL, isnull(@line_amount,'%') is '%'. And what is convert(int,'%')? 0 In the end you trick MSSQL to execute (ABS(line_amount) LIKE 0) and this doesn't work at all.

If you want to make a LIKE search on numbers, convert the numbers to strings, not the pattern to a number. How could that work? How?

Bye, Olaf.

 

[djj55]

OlafDoschke, good catch. I focused on the NULL not the underlining problem.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

Let's see, even if @line_amount would be any int value, you'd convert int to int, and get LIKE 9999 or whatever number.

Bye, Olaf.

 

[gk53]

why not just
(cast(ABS(line_amount) as varchar) LIKE @line_amount)