I am not sure how to make a query to take a field called word from tblwords and to add up the combined ascii code numbers.
so if the string in field word is:
'My word'
then the field AsciiEncode is populated with 674
i did find this on the ms website but not sure how to amalgamate that process into a query.
SET TEXTSIZE 0; SET NOCOUNT ON; -- Create the variables for the current character string position -- and for the character string. DECLARE @position int, @string char(15); -- Initialize the variables. SET @position = 1; SET @string = 'Du monde entier'; WHILE @position <= DATALENGTH(@string) BEGIN SELECT ASCII(SUBSTRING(@string, @position, 1)), CHAR(ASCII(SUBSTRING(@string, @position, 1))) SET @position = @position + 1 END; SET NOCOUNT OFF; GO
You can encapsulate this logic into a scalar UDF and use that in the query as shown below:
--Safety CheckIF OBJECT_ID('dbo.func_ASCIIEncodeString','FN') IS NOT NULLBEGIN DROP FUNCTION dbo.func_ASCIIEncodeStringENDGO--Create the functionCREATE FUNCTION dbo.func_ASCIIEncodeString (@inputString VARCHAR(15)) RETURNS INTASBEGIN -- Create the variables for the current character string position DECLARE @position INT = 1; DECLARE @ASCIIEncodedValue INT = 0; -- Initialize the variables. SET @position = 1; WHILE @position <= DATALENGTH(@inputString) BEGIN --Debug Point --SELECT ASCII(SUBSTRING(@inputString, @position, 1)) AS ASCIIValue, -- CHAR(ASCII(SUBSTRING(@inputString, @position, 1))) AS CharacterValue --Add the ASCII value of the currently read character into the return variable SELECT @ASCIIEncodedValue += ASCII(SUBSTRING(@inputString, @position, 1)); SET @position = @position + 1; END --Finally return to caller RETURN @ASCIIEncodedValue;ENDGO /********************** Testing ********************/--Test data structureDECLARE @sampleInput TABLE (stringToEncode VARCHAR(15));INSERT INTO @sampleInput (stringToEncode)VALUES ('A'), ('B'), ('AB'), ('ASCII'), ('SQL'), ('Nakul');--Check ASCII conversionSELECT si.stringToEncode AS StringToEncode, dbo.func_ASCIIEncodeString(si.stringToEncode) AS EncodedValueFROM @sampleInput AS si;/* RESULTSStringToEncode EncodedValue--------------- ------------A 65B 66AB 131ASCII 361SQL 240Nakul 507*/
IMPORTANT: One of the things I have changed in the script is to the datatype of the input string from CHAR to VARCHAR. Since CHAR is a fixed-length datatype, the WHILE loop would run for the specified number of characters with the blanks being reported as a space (ASCII 32) - even if the actual number of characters in the string is less (e.g. "Nakul" is only 5 characters, but the loop would run for 5 characters of the string + 10 spaces = 15 characters). You can change it back to CHAR if mandated as a business requirement.
DECLARE @intValue AS VARCHAR(100) = 'My word'SELECT SUM( ASCII(SUBSTRING(@intValue,number,1)) ) ASCIICounts FROM( SELECT DISTINCT number FROM MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@intValue) ) x
The problem with master..spt_values is that the executing user needs permissions to the master database - something that would not be the case in production environments.
A good alternate would be to use a similar tally table that exists in the user database itself.
Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Do I have to create the function first and run the sql to have the function stored in the db? I am having trouble understanding that bit as in ID: 41792646
then after that I can
select word, @sampleInput (word)
from tblwords
--Safety CheckIF OBJECT_ID('dbo.func_ASCIIEncodeString','FN') IS NOT NULLBEGIN DROP FUNCTION dbo.func_ASCIIEncodeStringENDGO--Create the functionCREATE FUNCTION dbo.func_ASCIIEncodeString (@inputString VARCHAR(15)) RETURNS INTASBEGIN -- Create the variables for the current character string position DECLARE @position INT = 1; DECLARE @ASCIIEncodedValue INT = 0; -- Initialize the variables. SET @position = 1; WHILE @position <= DATALENGTH(@inputString) BEGIN --Debug Point --SELECT ASCII(SUBSTRING(@inputString, @position, 1)) AS ASCIIValue, -- CHAR(ASCII(SUBSTRING(@inputString, @position, 1))) AS CharacterValue --Add the ASCII value of the currently read character into the return variable SELECT @ASCIIEncodedValue += ASCII(SUBSTRING(@inputString, @position, 1)); SET @position = @position + 1; END --Finally return to caller RETURN @ASCIIEncodedValue;END
it got partially thru and now halted with error:
I am just seeing what happened if i can find it
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
IT issues often require a personalized solution. With Ask the Experts™, submit your questions to our certified professionals and receive unlimited, customized solutions that work for you.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial