Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Covert all characters in field into their ASCII code Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 9:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:46 AM
Points: 158, Visits: 504
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.


Post #1565991
Posted Tuesday, April 29, 2014 9:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
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)
Post #1566007
Posted Tuesday, April 29, 2014 9:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 2,000, Visits: 5,200
Sean is quicker typing
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

Post #1566009
Posted Tuesday, April 29, 2014 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
Eirikur Eiriksson (4/29/2014)
Sean is quicker typing


Only this time my friend.


_______________________________________________________________

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)
Post #1566018
Posted Tuesday, April 29, 2014 9:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 2,000, Visits: 5,200
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.

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
Post #1566020
Posted Tuesday, April 29, 2014 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
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.

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)
Post #1566027
Posted Tuesday, April 29, 2014 10:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
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



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

Anon.

Post #1566031
Posted Tuesday, April 29, 2014 10:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 2,000, Visits: 5,200
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


What about Base36, almost closes the circle
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
Post #1566042
Posted Tuesday, April 29, 2014 10:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
Eirikur Eiriksson (4/29/2014)
What about Base36, almost closes the circle


FOTFL

Nice



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

Anon.

Post #1566046
Posted Tuesday, April 29, 2014 12:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,098, Visits: 3,155
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1566100
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse