Solved

sql server query

Posted on 2016-09-10
9
92 Views
Last Modified: 2016-09-11
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.
0
Comment
Question by:PeterBaileyUk
  • 5
  • 3
9 Comments
 
LVL 12

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 41792646
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.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41792700
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

0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41792746
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:PeterBaileyUk
ID: 41793138
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
0
 

Author Comment

by:PeterBaileyUk
ID: 41793140
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

0
 

Author Comment

by:PeterBaileyUk
ID: 41793146
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

0
 

Author Comment

by:PeterBaileyUk
ID: 41793156
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.
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41793172
thank you Nakul
0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41793265
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

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now