Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Covert all characters in field into their ASCII code


Covert all characters in field into their ASCII code

Author
Message
LoosinMaMind
LoosinMaMind
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 565
Hi i have a column with the following data in .....

The data varies from numeric to capitals to lower case but never exceeds 5 characters in length

Sample Data

02yC
12G8
9Pp1
7@uL


How can I convert each character into its ASCII equivalent?

Expected Results

485012167
49507156
578011249
556411776

Any advice greatly received.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
You can do this using a tally table. Please post ddl and sample data in a consumable format...it makes it a lot easier for us to work on your issue instead of turning your post into something we can use.


;with myData as
(
select '02yC' as SomeValue union all
select '12G8' union all
select '9Pp1' union all
select '7@uL'
)
,
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)

select SomeValue,
STUFF((
select ascii(SUBSTRING(d2.SomeValue, N, 1))
from myData d2
join cteTally t on t.N <= DATALENGTH(d2.SomeValue)
where d2.SomeValue = d.SomeValue
order by N
for xml path('')), 1, 0,'')
from myData d
group by d.SomeValue



What I don't really understand here is what is the point of this? This data is pretty much unusable because you have no way of putting it back together again.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6762 Visits: 17733
Sean is quicker typing :-D
DECLARE @STRINGS TABLE
(
MYID INT IDENTITY(1,1) NOT NULL
,MYSTRING CHAR(4) NOT NULL
)
INSERT INTO @STRINGS (MYSTRING)
SELECT MYSTRING FROM
(VALUES ('02yC'),('12G8'),('9Pp1'),('7@uL')) AS X(MYSTRING);
;WITH NX(N) AS (SELECT N FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,NUMBERS(N) AS
(
SELECT ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N
FROM NX N1,NX N2
)
SELECT
MYID
,(SELECT
UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))
FROM @STRINGS MS
CROSS APPLY NUMBERS NM
WHERE NM.N <= LEN(MS.MYSTRING)
AND MS.MYID = MMS.MYID
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(100)') AS NUMSTR
FROM @STRINGS MMS


Cool
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
Eirikur Eiriksson (4/29/2014)
Sean is quicker typing :-D


Only this time my friend. Cool

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6762 Visits: 17733
Sean Lange (4/29/2014)

What I don't really understand here is what is the point of this? This data is pretty much unusable because you have no way of putting it back together again.


Probably a string of hex values would be better, at least every character representation is then of the same length.
Cool
DECLARE @STRINGS TABLE
(
MYID INT IDENTITY(1,1) NOT NULL
,MYSTRING CHAR(5) NOT NULL
)
INSERT INTO @STRINGS (MYSTRING)
SELECT MYSTRING FROM
(VALUES ('02yC'),('12G8'),('9Pp1'),('7@uL')) AS X(MYSTRING);
SELECT
MS.MYID
,MS.MYSTRING
,SUBSTRING(CONVERT(VARCHAR(10),CAST(MS.MYSTRING AS VARBINARY(10)),1),3,10)
FROM @STRINGS MS



Results
MYID        MYSTRING HEX_STRING
----------- -------- ----------
1 02yC 30327943
2 12G8 31324738
3 9Pp1 39507031
4 7@uL 3740754C

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
Eirikur Eiriksson (4/29/2014)
Sean Lange (4/29/2014)

What I don't really understand here is what is the point of this? This data is pretty much unusable because you have no way of putting it back together again.


Probably a string of hex values would be better, at least every character representation is then of the same length.
Cool
DECLARE @STRINGS TABLE
(
MYID INT IDENTITY(1,1) NOT NULL
,MYSTRING CHAR(5) NOT NULL
)
INSERT INTO @STRINGS (MYSTRING)
SELECT MYSTRING FROM
(VALUES ('02yC'),('12G8'),('9Pp1'),('7@uL')) AS X(MYSTRING);
SELECT
MS.MYID
,MS.MYSTRING
,SUBSTRING(CONVERT(VARCHAR(10),CAST(MS.MYSTRING AS VARBINARY(10)),1),3,10)
FROM @STRINGS MS



Results
MYID        MYSTRING HEX_STRING
----------- -------- ----------
1 02yC 30327943
2 12G8 31324738
3 9Pp1 39507031
4 7@uL 3740754C


At you can put it back together. Still seems like far more effort than it is worth. We have effectively doubled the length of the string so we have to push twice as much data around. Ugh!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7966 Visits: 9425
Eirikur Eiriksson (4/29/2014)
Probably a string of hex values would be better, at least every character representation is then of the same length.


Meh! use octal :-P


Far away is close at hand in the images of elsewhere.

Anon.


Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6762 Visits: 17733
David Burrows (4/29/2014)
Eirikur Eiriksson (4/29/2014)
Probably a string of hex values would be better, at least every character representation is then of the same length.


Meh! use octal :-P


What about Base36, almost closes the circle:-D
DECLARE @STRINGS TABLE
(
MYID INT IDENTITY(1,1) NOT NULL
,MYSTRING CHAR(4) NOT NULL
)
INSERT INTO @STRINGS (MYSTRING)
SELECT MYSTRING FROM
(VALUES ('02yC'),('12G8'),('9Pp1'),('7@uL')) AS X(MYSTRING);

;WITH NX(N) AS (SELECT N FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,NUMBERS(N) AS
(
SELECT ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N
FROM NX N1,NX N2
)

SELECT
MYID
,(SELECT
CASE
WHEN ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) / 36) < 10 THEN CHAR(48 + ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) / 36))
ELSE CHAR(97 + (((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) / 8) - 10))
END
+ CASE
WHEN ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) % 36) < 10 THEN CHAR(48 + ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) % 36))
ELSE CHAR(97 + (((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) % 36) - 10))
END
FROM @STRINGS MS
CROSS APPLY NUMBERS NM
WHERE NM.N <= LEN(MS.MYSTRING)
AND MS.MYID = MMS.MYID
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(100)') AS NUMSTR
FROM @STRINGS MMS



Results
MYID        NUMSTR
----------- ---------
1 1c1e3d1v
2 1d1e1z1k
3 1l28341d
4 1j1s3924

David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7966 Visits: 9425
Eirikur Eiriksson (4/29/2014)
What about Base36, almost closes the circle:-D


FOTFL w00t

Nice ;-)


Far away is close at hand in the images of elsewhere.

Anon.


ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
For only 5 chars, I wouldn't bother will all the CTEs and related folderol.

Why not just:



SELECT
ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 2, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 3, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 4, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 5, 1)) AS varchar(3)), '')
FROM (
SELECT '02yC' AS data UNION ALL
SELECT '12G8' UNION ALL
SELECT '9Pp1' UNION ALL
SELECT '7@uL' UNION ALL
SELECT '' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b2' UNION ALL
SELECT 'c34' UNION ALL
SELECT 'd456' UNION ALL
SELECT 'e5678'
) AS test_data




SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search