To test out the function, try: SELECT dbo.fn_urlencode('Here''s a (test) string') Replace the non-standard characters with URL encoded - equivalents: When no more - occurrences are found, PATINDEX() will return 0, and the WHILE - loop will end. Loop through the variable, using PATINDEX() to look - for non-standard characters using a wildcard. For each occurrence, we’re using the char2hex() function to calculate the character’s hex value, and then we replace that character in with the calculated hex equivalent. Replace % with %25, that way, we can skip replacing the - % character once we're looping through the string.Īnd here’s the main loop of the function: We’re using PATINDEX() to find any character that isn’t a regular A-Z, 0-9, a period, a dash or a percent character. If we don’t, we’ll find ourselves in an infinite loop that will replace % with %25, with %2525, with %252525, etc. CREATE FUNCTION varchar(max))įirst of all, we’ll have to replace all the percent characters in the string. Now, here’s the main URL encoding function, starting off with the usual declarations.
Ther reason for the “1+” is because SUBSTRING( xyz, 0, 1) will return a blank value, but we need it to be 0 in this case.Įdit: In a newer post, I demonstrate a more efficient method using the binary datatype. The SUBSTRING() construct is used to translate digits from 11 to 15 to their proper hex digits. The second hex digit is the remainder, in this case 33-32 = 1. The first hex digit is the decimal value minus its modulo 16, all divided by 16, the second digit is the remainder, the modulo 16 of the original decimal value: SUBSTRING('0123456789ABCDEF', 1+(, function returns a char(2) scalar value, which means that we’re only translating characters from CHAR(0) to CHAR(255) to a two-digit hex value (00-FF). This is the most compact char-to-hex function I could construct. A function to convert a character to its hex value It breaks down into two functions, the URL encoding function, and a decimal-to-hex function. Calculating hex values from decimal values.So in order to make this work, we’ll negotiate a number or challenges in T-SQL, including 39 decimal is 27 hex (2×16 + 7), so then the URL encoded apostrophe is %27. Should be translated as Here%27s%20a%20%28test%29%20stringįor example, the apostrophe is equivalent to CHAR(39). To URL encode a string, you translate special characters to their ascii value, turn that into a hexadecimal value, then prefix that value with a percent sign, so the following string Here's a (test) string URL encoding is what happens when you translate special characters (basically anything that isn’t an alphanumerical) so they’ll fit in a URL. SET = CHARINDEX( CHAR(13) + CHAR(10), + 2, RETURN TOP 10ĭbo.udfRemoveCodeComments(OBJECT_DEFINITION(o.object_id)) AS DefA while ago, I needed to create a URL encoding function in T-SQL. You can beautifully indent them and remove the unnecessary or commented code.ĬREATE FUNCTION dbo.udfRemoveCodeComments ( VARCHAR( MAX) )ĪS BEGIN WHILE CHARINDEX( '/*', > 0 OR CHARINDEX( '-', > 0
This would help another user or programmer to understand the business rules & functionality. Change/Ref ID | Date | User | Change Details Other than this I would love to include a Revision History that shows me when & who modified the proc & for which purpose, in following format. On contrary I would love to have those comments that shows the: Other thing is, why do you want to remove these comments? Syscomments is just a view that shows DB objects definition from the metadata, updating text column won't modify the procs.
That means changing the value in the text field from syscomments from:
0 kommentar(er)
