Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate a checkletter (code) Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 2:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
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
Post #1347637
Posted Tuesday, August 21, 2012 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347799
Posted Tuesday, August 21, 2012 10:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
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.
Post #1347910
Posted Tuesday, August 21, 2012 10:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347915
Posted Tuesday, August 21, 2012 10:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
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...
Post #1347930
Posted Tuesday, August 21, 2012 10:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1347936
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse