January 22, 2003 at 2:27 pm
I am calculating the check digit for an address file. The problem is that I declare the following.
DECLARE @string CHAR(11)
set @string = '1A097686283'
Its suppose to calculate one digit and put it on the end of the string above for my check digit for a total of 12 chars. I get the following error:
Server: Msg 245, Level 16, State 1, Line 5
Syntax error converting the varchar value 'A' to a column of data type int.
I tried removing everything int related but still getting this error. HELP!!
SELECT('#'+@string+Cast(
-- First Position
Case
When Cast(right((-- First Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2
End +
-- Third Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2
End +
-- Fifth Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2
End +
-- Seventh Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2
End +
-- Ninth Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2
End +
-- Eleventh Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2
End
+
Substring(Ltrim(Rtrim('0' + @string)), 4,1) +
Substring(Ltrim(Rtrim('0' + @string)), 6,1) +
Substring(Ltrim(Rtrim('0' + @string)), 8,1)),1)
as int)
+
Case
When Substring(@string,2,1)= 'A'
Then 1
When Substring(@string,2,1) = 'B'
Then 2
When Substring(@string,2,1) = 'C'
Then 3
When Substring(@string,2,1) = 'D'
Then 4
When Substring(@string,2,1) = 'E'
Then 5
When Substring(@string,2,1) = 'F'
Then 6
When Substring(@string,2,1) = 'G'
Then 7
When Substring(@string,2,1) = 'H'
Then 8
When Substring(@string,2,1) = 'I'
Then 9
When Substring(@string,2,1) = 'J'
Then 1
When Substring(@string,2,1) = 'K'
Then 2
When Substring(@string,2,1) = 'L'
Then 3
When Substring(@string,2,1) = 'M'
Then 4
When Substring(@string,2,1) = 'N'
Then 5
When Substring(@string,2,1) = 'O'
Then 6
End = 0 then 0
Else
10 -
Cast(
right((
-- First Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2
End +
-- Third Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 3,1) * 2
End +
-- Fifth Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 5,1) * 2
End +
-- Seventh Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 7,1) * 2
End +
-- Ninth Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 9,1) * 2
End +
-- Eleventh Position
Case
When Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 11,1) * 2
End +
Substring(Ltrim(Rtrim('0' + @string)), 4,1) +
Substring(Ltrim(Rtrim('0' + @string)), 6,1) +
Substring(Ltrim(Rtrim('0' + @string)), 8,1)),1)
as int)
End as Varchar)
+
Case
When Substring(@string,2,1) = 'A'
Then 1
When Substring(@string,2,1) = 'B'
Then 2
When Substring(@string,2,1) = 'C'
Then 3
When Substring(@string,2,1) = 'D'
Then 4
When Substring(@string,2,1) = 'E'
Then 5
When Substring(@string,2,1) = 'F'
Then 6
When Substring(@string,2,1) = 'G'
Then 7
When Substring(@string,2,1) = 'H'
Then 8
When Substring(@string,2,1) = 'I'
Then 9
When Substring(@string,2,1) = 'J'
Then 1
When Substring(@string,2,1) = 'K'
Then 2
When Substring(@string,2,1) = 'L'
Then 3
When Substring(@string,2,1) = 'M'
Then 4
When Substring(@string,2,1) = 'N'
Then 5
When Substring(@string,2,1) = 'O'
Then 6
End
)+'#' as ACS_CheckDigit
January 22, 2003 at 2:53 pm
Looks like it comes from the following sub statement...
Substring(Ltrim(Rtrim('0' + @string)), 3,1)
eg..
DECLARE @string CHAR(11)
set @string = '1A097686283'
print Substring(Ltrim(Rtrim('0' + @string)), 3,1)
select Substring(Ltrim(Rtrim('0' + @string)), 3,1) *2
When you try to multiply that by an integer, SQL Server throws the error
January 22, 2003 at 2:56 pm
If possible can you post the actual rule(s) how it to calculated the checkdigit. I think some posters will be able to shorten the statement.
January 22, 2003 at 3:29 pm
There is a lot of confusing things going on here. Like why do you keep doing Ltrim(Rtrim( the data has no spaces in it or on front or end?
Also, when you do
Case
When Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2 > 9
Then Cast(Left((Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2),1) as int)
+ Cast(Right((Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2),1) as int)
Else Substring(Ltrim(Rtrim('0' + @string)), 1,1) * 2
End +
Substring 1,1 will always be the 0 you concatinated on.
I think you logic may not be what you think. Perhaps you are meaning 2,1 and for 3,1 you mean 4,1.
As jhara point out that is why you hit A in the code. Because
when you do '0' + '1A097686283' you get
'01A097686283'
And string position 3 is the 'A'.
I think you need to recheck your logic and as suggested post here maybe what should happend and how it should be done so we can offer help.
January 22, 2003 at 4:09 pm
quote:
If possible can you post the actual rule(s) how it to calculated the checkdigit. I think some posters will be able to shorten the statement.
yeah I think you could do it with a slightly shorter query:
DECLARE @string CHAR(11)
set @string = '1A097686283'
select @String + CHAR(ABS(checksum(@string)) % 255)
Maybe not exactly what you need but I'm sure you could use checksum() in some way to fit your requirements?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply