VB icon

SQL GUID 2 String (Compression)

Email
Submitted on: 1/24/2015 10:10:00 AM
By: Trevor Herselman (from psc cd)  
Level: Intermediate
User Rating: By 2 Users
Compatibility: VB 6.0
Views: 706
 
     2 functions to convert a 38 character GUID eg. {2E1EFAD8-4AD9-48A6-B9A9-75505F2B9A51} to a compact/compressed 16 character VB String eg. @#$%<|{.#&(@*^&! and back again. I use these 2 functions to efficiently convert literally millions of my SQL uniqueidentifier variables to their 16 character VB String equivalents for storage in arrays. This code is from my SQL & VB based game which stores millions of these GUID's, I needed a more efficient way to store them as my memory requirements are already running into gigs. The code has been slightly modified for PSC (no input/debug checks). If anyone finds it useful or has any kind of speed improvement or has questions, please let me know !!! If you find this code/concept good/useful, or don't understand, or have any performance suggestions (besides using API calls), just let me know. NB. With the help of Ian Webling's AWESOME advice and re-coding, this is the latest version using concepts from both of us, and has been heavily modified from the first release. MAJOR credit and thank you's must again be paid to Ian for his optimization skill !!! Ian collectively improved their speed by an estimated 70%, and I provided an additional 30% increase on his code, both calculated on 100,000 conversions. If U want MORE power/speed, check my API versions.
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
'**************************************
' Name: SQL GUID 2 String (Compression)
' Description:2 functions to convert a 38 character GUID eg. {2E1EFAD8-4AD9-48A6-B9A9-75505F2B9A51} to a compact/compressed 16 character VB String eg. @#$%<|{.#&(@*^&! and back again. I use these 2 functions to efficiently convert literally millions of my SQL uniqueidentifier variables to their 16 character VB String equivalents for storage in arrays. This code is from my SQL & VB based game which stores millions of these GUID's, I needed a more efficient way to store them as my memory requirements are already running into gigs. The code has been slightly modified for PSC (no input/debug checks). If anyone finds it useful or has any kind of speed improvement or has questions, please let me know !!! If you find this code/concept good/useful, or don't understand, or have any performance suggestions (besides using API calls), just let me know. NB. With the help of Ian Webling's AWESOME advice and re-coding, this is the latest version using concepts from both of us, and has been heavily modified from the first release. MAJOR credit and thank you's must again be paid to Ian for his optimization skill !!! Ian collectively improved their speed by an estimated 70%, and I provided an additional 30% increase on his code, both calculated on 100,000 conversions. If U want MORE power/speed, check my API versions.
' By: Trevor Herselman (from psc cd)
'
' Assumes:String concatenation, using &, reduced my first version's speed by over 20% for 100,000 conversions. The iif statement has been replaced by a MUCH faster Format$ and Replace$ combination. Uses the Space$(16) VB Function to create a Buffer of spaces in GUID2ID function, 1.5% faster than using String$(16, 32) function. 99% of this code is attributed to the enhancements and suggestions made by Ian Webling.
'**************************************

Public Function GUID2ID(ByRef InGUID As String) As String
 Dim Hex As String * 4
 Let GUID2ID = Space$(16)
 Let Hex = "&H00"
 Mid$(Hex, 3, 2) = Mid$(InGUID, 2, 2)
 Mid$(GUID2ID, 1, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 4, 2)
 Mid$(GUID2ID, 2, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 6, 2)
 Mid$(GUID2ID, 3, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 8, 2)
 Mid$(GUID2ID, 4, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 11, 2)
 Mid$(GUID2ID, 5, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 13, 2)
 Mid$(GUID2ID, 6, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 16, 2)
 Mid$(GUID2ID, 7, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 18, 2)
 Mid$(GUID2ID, 8, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 21, 2)
 Mid$(GUID2ID, 9, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 23, 2)
 Mid$(GUID2ID, 10, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 26, 2)
 Mid$(GUID2ID, 11, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 28, 2)
 Mid$(GUID2ID, 12, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 30, 2)
 Mid$(GUID2ID, 13, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 32, 2)
 Mid$(GUID2ID, 14, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 34, 2)
 Mid$(GUID2ID, 15, 1) = Chr$(CLng(Hex))
 Mid$(Hex, 3, 2) = Mid$(InGUID, 36, 2)
 Mid$(GUID2ID, 16, 1) = Chr$(CLng(Hex))
End Function
Public Function ID2GUID(ByRef InID As String) As String
 Let ID2GUID = "{12345678-1234-1234-1234-123456789012}"
 Mid$(ID2GUID, 2, 2) = Format$(Hex$(Asc(InID)), "@@")
 Mid$(ID2GUID, 4, 2) = Format$(Hex$(Asc(Right$(InID, 15))), "@@")
 Mid$(ID2GUID, 6, 2) = Format$(Hex$(Asc(Right$(InID, 14))), "@@")
 Mid$(ID2GUID, 8, 2) = Format$(Hex$(Asc(Right$(InID, 13))), "@@")
 Mid$(ID2GUID, 11, 2) = Format$(Hex$(Asc(Right$(InID, 12))), "@@")
 Mid$(ID2GUID, 13, 2) = Format$(Hex$(Asc(Right$(InID, 11))), "@@")
 Mid$(ID2GUID, 16, 2) = Format$(Hex$(Asc(Right$(InID, 10))), "@@")
 Mid$(ID2GUID, 18, 2) = Format$(Hex$(Asc(Right$(InID, 9))), "@@")
 Mid$(ID2GUID, 21, 2) = Format$(Hex$(Asc(Right$(InID, 8))), "@@")
 Mid$(ID2GUID, 23, 2) = Format$(Hex$(Asc(Right$(InID, 7))), "@@")
 Mid$(ID2GUID, 26, 2) = Format$(Hex$(Asc(Right$(InID, 6))), "@@")
 Mid$(ID2GUID, 28, 2) = Format$(Hex$(Asc(Right$(InID, 5))), "@@")
 Mid$(ID2GUID, 30, 2) = Format$(Hex$(Asc(Right$(InID, 4))), "@@")
 Mid$(ID2GUID, 32, 2) = Format$(Hex$(Asc(Right$(InID, 3))), "@@")
 Mid$(ID2GUID, 34, 2) = Format$(Hex$(Asc(Right$(InID, 2))), "@@")
 Mid$(ID2GUID, 36, 2) = Format$(Hex$(Asc(Right$(InID, 1))), "@@")
 Let ID2GUID = Replace$(ID2GUID, " ", "0")
End Function


Other 3 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.