July 7, 2003 at 9:54 am
I have a unique circumstance where I need to do a Select in one table and one of the fields can contain a case-sensitive varchar.
The purpose is to prevent duplicate entries on an import and this field can be the same, but lower case is an entry and upper indicates reversal (go figure).
Is there a 'better' method other than doing an ASCII check on the field?
Currently I use:
SELECT COUNT(1) FROM TableName WHERE Field1=@Var1 AND Field2=@Var2 AND ASCII(Field1)=ASCII(@Var1)
However- I have an issue putting this into a DTS Package, specifically performing ASCII on a parameter.
I tried:
SELECT COUNT(1) FROM TableName WHERE Field1=? AND Field2=? AND ASCII(Field1)=ASCII(?)
But that generates an error on the ASCII(?) of a parameter.
Suggestions on how to do this in a DTS script using params?
Edited by - Tim OPry on 07/07/2003 10:08:10 AM
July 8, 2003 at 12:47 am
you can rewrite it by converting the fields into varbinary.
SELECT COUNT(1) FROM TableName WHERE Field1=@Var1 AND Field2=@Var2 AND convert(varbinary(30),Field1)=convert(varbinary(30),@Var1)
remember Ascii function will only look for the first character Ascii value.
Edited by - nazim on 07/08/2003 12:53:27 AM
July 8, 2003 at 5:53 am
Thank you for the reply.
Since the input data is very limited and the first character is unique, the ASCII allowed for simply checking of upper/lower case.
The problem is in the DTS Execute SQL Task.
It apparently does not allow the use of any native SQL functions (ASCII, Convert, etc) in conjunction with an input parameter.
I imagine I can change it to an sProc and put the count value in a return param and evaluate based upon that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply