August 21, 2012 at 2:41 am
I'm trying to translate a stored procedure written in SQL to do the same in VBA-Access. Code is attached. Although I mostly get what it does (for a number like 1234, generate a letter), some details are missing.
Could you describe in letter the flowgram (specially command in italics-bold) for '1234'? What happens in iterations higher than 5 for this number (1234)? Thanks in advance, a.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CheckLetter] ( @ID INT )
RETURNS VARCHAR
AS BEGIN
DECLARE @Result VARCHAR
DECLARE @abc VARCHAR(23)
DECLARE @Mod INT
DECLARE @I INT
DECLARE @Num INT
SET @Mod = 0
SET @I = 1
WHILE ( @I < 7 )
BEGIN
SET @Mod = @Mod + ( @I
* CONVERT(INT, SUBSTRING(CONVERT(VARCHAR, @ID),
@I, 1)) )
SET @I = @I + 1
END
SET @Mod = @Mod % 23
SET @abc = 'ABCDEFGHJKLMNPQRSTVWXYZ'
-- Add the T-SQL statements to compute the return value here
SELECT @Result = SUBSTRING(@ABC, @Mod + 1, 1)
-- Return the result of the function
RETURN @Result
END
August 21, 2012 at 7:49 am
Not really sure what your question is but you can read about substring here. http://msdn.microsoft.com/en-us/library/ms187748.aspx
What are the business rules for this? This looks pretty strange.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2012 at 10:08 am
Thanks Sean. By now I've got a very clear idea of what the (mod 23) code does in SQL (code in VBA still pending).
Basically it takes a number 'ID' like '1234' and calculates the following in each iteration 'i':
i | mod | mod + i*substring(id,i1)
1 | 0 0+1 *1=1
2 | 1 | 1+2*2 = 5
.........
7 | .... | 30
Then 30 mod 23 = 7 --> and 7+1 would be the value used for looking up the corresponding letter, an H ("ABCDEFGHJKLMNPQRSTVWXYZ"). This can be translated into VBA relatively easily, since Substring is equivalent to Excel function Mid.
Hope this helps someone, a.
August 21, 2012 at 10:16 am
a_ud (8/21/2012)
Thanks Sean. By now I've got a very clear idea of what the (mod 23) code does in SQL (code in VBA still pending).Basically it takes a number 'ID' like '1234' and calculates the following in each iteration 'i':
i | mod | mod + i*substring(id,i1)
1 | 0 0+1 *1=1
2 | 1 | 1+2*2 = 5
.........
7 | .... | 30
Then 30 mod 23 = 7 --> and 7+1 would be the value used for looking up the corresponding letter, an H ("ABCDEFGHJKLMNPQRSTVWXYZ"). This can be translated into VBA relatively easily, since Substring is equivalent to Excel function Mid.
Hope this helps someone, a.
I can understand the sql part but what I can't understand is what the business rule for this is. It is a very strange algorithm to pick a letter. I am curious what this is used for and the logic is the way it is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2012 at 10:33 am
It's quite standard (at least in Europe!), it's basically a check letter that you generate for verifying a sequence of numbers (like the control digit in a check, a barcode, or a letter for an ID card).
Once you've got the final mod and letter (for '1234' was H), you'd add that to the sequence of nos. So the full check-ID number would read: 1234-H.
Some devices like barcode readers might have code checking that the letter matches the nos, that's all...
August 21, 2012 at 10:39 am
a_ud (8/21/2012)
It's quite standard (at least in Europe!), it's basically a check letter that you generate for verifying a sequence of numbers (like the control digit in a check, a barcode, or a letter for an ID card).Once you've got the final mod and letter (for '1234' was H), you'd add that to the sequence of nos. So the full check-ID number would read: 1234-H.
Some devices like barcode readers might have code checking that the letter matches the nos, that's all...
Ahh I gotcha. Makes sense now. 😉 The code itself was easy enough to decipher I just couldn't figure out what the point was.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply