Solved

sql server rotate through word

Posted on 2016-09-15
2
30 Views
Last Modified: 2016-09-15
I had this question after viewing sql server query.

The above id works perfectly and is at sentence level.

what I would like now to do is rotate just through a word alone and get its sequence.

I have query that gives this:
ClientCode      word
54553002      OCEAN
54553002      RACE
54553002      T6
54553002      XC90

for each word I would like to grab the sequence of chars ascii so ocean has a column with
O=79
C=67
E=69
A=65
N=78

79,67, 69, 65, 78

the following gets the ascii code already.
USE [Dictionary]
GO
/****** Object:  UserDefinedFunction [dbo].[func_ASCIIEncodeString]    Script Date: 15/09/2016 16:44:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Create the function
ALTER 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
Comment
Question by:PeterBaileyUk
2 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41799978
First create this function:
CREATE FUNCTION [dbo].[fnTally]()
RETURNS TABLE 
AS
/*******************************************************************************\
Function	: fnTally

Purpose		: returns a set with numbers from 1 to 10,000 
			  to be used in parsing and sequential data generation whithout loop
			  
Parameters	: no parameters

Invoke		:
	
		select * from [dbo].[fnTally]()
		select N from [dbo].[fnTally]()
		select substring('abcdef',N,1) as chr from [dbo].[fnTally]() where N<len('abcdef') -- parsing a string
		select dateadd(dd, N, '2007-01-01') as dte from [dbo].[fnTally]() --gets dates for about 30 years

\*******************************************************************************/
RETURN
	WITH 
	E1(N) AS 
	( --10E+1 or 10 rows
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
	),                         
   E2(N) AS 
   ( --10E+2 or 100 rows	
		SELECT 1 FROM E1 a, E1 b
	),
   E4(N) AS 
   ( --10E+4 or 10,000 rows max
		SELECT 1 FROM E2 a, E2 b
	)
			 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N FROM E4
	;

GO

Open in new window

And then use it like this:
declare
	@string varchar(1000)='what I would like now to do is rotate just through a word alone and get its sequence'

select 
	(
		select 
			cast(ascii(substring(@string,N,1)) as varchar(5))+','
		from 
			[zb_dba_maint].[dbo].[fnTally]() nr
		where
			nr.N<=len(@string) 
		FOR XML PATH('')
	) as ascii_seq

-- result:
ascii_seq
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
119,104,97,116,32,73,32,119,111,117,108,100,32,108,105,107,101,32,110,111,119,32,116,111,32,100,111,32,105,115,32,114,111,116,97,116,101,32,106,117,115,116,32,116,104,114,111,117,103,104,32,97,32,119,111,114,100,32,97,108,111,110,101,32,97,110,100,32,103,101,116,32,105,116,115,32,115,101,113,117,101,110,99,101,

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41800085
thank you
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

20 Experts available now in Live!

Get 1:1 Help Now