Status: Solved
Priority: Medium
Security: Public
Views: 144
Last Modified: 2016-09-11

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.

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

Open in new window

I am just experimenting with an idea, i may at some stage need to store those codes in a field 77-121-32-119-111-114-100 but i am not sure yet.

PeterBaileyUk

Asked: 2016-09-10

Advertise Here

Microsoft SQL Server

1 Solution

LVL 15

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented: 2016-09-10

You can encapsulate this logic into a scalar UDF and use that in the query as shown below:

--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
GO 

/********************** Testing ********************/
--Test data structure
DECLARE @sampleInput TABLE (stringToEncode VARCHAR(15));

INSERT INTO @sampleInput (stringToEncode)
VALUES ('A'),
       ('B'),
       ('AB'),
       ('ASCII'),
       ('SQL'),
       ('Nakul');

--Check ASCII conversion
SELECT si.stringToEncode AS StringToEncode,
       dbo.func_ASCIIEncodeString(si.stringToEncode) AS EncodedValue
FROM @sampleInput AS si;

/* RESULTS

StringToEncode  EncodedValue
--------------- ------------
A               65
B               66
AB              131
ASCII           361
SQL             240
Nakul           507

*/

Open in new window

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.

LVL 38

Pawan KumarDatabase ExpertCommented: 2016-09-10

Use below simple query to get this..

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

Open in new window

LVL 15

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented: 2016-09-10

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.

PeterBaileyUkAuthor Commented: 2016-09-10

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

PeterBaileyUkAuthor Commented: 2016-09-10

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

Open in new window

PeterBaileyUkAuthor Commented: 2016-09-10

I am upto here

its not quite right I am stuck in vb function mentality

use Dictionary

insert into TblWords(AsciiEncode)
selectdbo.func_ASCIIEncodeString(Word)
from TblWords

Open in new window

PeterBaileyUkAuthor Commented: 2016-09-10

I am doing this it should be an update so calling like this:

use Dictionary

UPDATE tblwords
SET AsciiEncode = (
select dbo.func_ASCIIEncodeString(Word)
from TblWords)

Open in new window

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.

PeterBaileyUkAuthor Commented: 2016-09-11

thank you Nakul

LVL 15

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented: 2016-09-11

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

sql server query

Join & Write a Comment Already a member? Login.

Featured Post