ASCII VALUE IN SQL SERVER

CREATE PROC ASCII_SP  

      

(@STRING VARCHAR(100))

         

AS

BEGIN  

CREATE TABLE #ASCII_VALUE

(ASCII_NO INT NULL,

 LETTER VARCHAR (100))

SET TEXTSIZE 0         

SET NOCOUNT ON         

        

DECLARE @POSITION INT        

SET @POSITION = 1

        

WHILE @POSITION <= DATALENGTH(@STRING)         

BEGIN         

INSERT INTO #ASCII_VALUE         

SELECT ASCII(SUBSTRING(@STRING, @POSITION, 1)),         

CHAR(ASCII(SUBSTRING(@STRING, @POSITION, 1)))

       

SET @POSITION = @POSITION + 1         

END

        

UPDATE #ASCII_VALUE SET LETTER = '%', ASCII_NO = (SELECT ASCII('%')) WHERE LETTER = ''         

SET NOCOUNT OFF         

   

SELECT * FROM #ASCII_VALUE

DECLARE @NUM INT

SELECT @NUM = SUM(ASCII_NO) FROM #ASCII_VALUE

DECLARE @NAMES VARCHAR(8000)     

SELECT @NAMES = COALESCE(@NAMES +'','') + LETTER FROM #ASCII_VALUE

   

DELETE FROM #ASCII_VALUE WHERE ASCII_NO IS NOT NULL

  

INSERT INTO #ASCII_VALUE    

SELECT @NUM, @NAMES

SELECT * FROM #ASCII_VALUE

END         

GRANT EXEC ON ASCII_SP TO PUBLIC

--    EXEC ASCII_SP 'ARKA GUPTA'