asked on
sql server query
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.
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
Microsoft SQL Server
Last Comment
Nakul Vachhrajani
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
A good alternate would be to use a similar tally table that exists in the user database itself.
ASKER
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
then after that I can
select word, @sampleInput (word)
from tblwords
ASKER
i ran following with successful execution
--Safety Check
IF OBJECT_ID('dbo.func_ASCIIEncodeString','FN') IS NOT NULL
BEGIN
DROP FUNCTION dbo.func_ASCIIEncodeString
END
GO
--Create the function
CREATE FUNCTION dbo.func_ASCIIEncodeString (@inputString VARCHAR(15))
RETURNS INT
AS
BEGIN
-- 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
ASKER
I am upto here
its not quite right I am stuck in vb function mentality
its not quite right I am stuck in vb function mentality
use Dictionary
insert into TblWords(AsciiEncode)
selectdbo.func_ASCIIEncodeString(Word)
from TblWords
ASKER
I am doing this it should be an update so calling like this:
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.
use Dictionary
UPDATE tblwords
SET AsciiEncode = (
select dbo.func_ASCIIEncodeString(Word)
from TblWords)
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.
ASKER
thank you Nakul
You're welcome! To fix the sub-query error in your update statement, you can change it to the following:
UPDATE tblw
SET tblw.AsciiEncode = dbo.func_ASCIIEncodeString(tblw.Word)
FROM dbo.tblWords AS tblw
Open in new window