April 29, 2014 at 9:03 am
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.
April 29, 2014 at 9:26 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2014 at 9:29 am
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
😎
April 29, 2014 at 9:40 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2014 at 9:44 am
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
April 29, 2014 at 9:57 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2014 at 10:03 am
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.
April 29, 2014 at 10:17 am
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:-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
April 29, 2014 at 10:22 am
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.
April 29, 2014 at 12:54 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
April 29, 2014 at 1:01 pm
Actually in my system I wouldn't have all this nonsensical effort. I have a view named cteTally so I never have to build all that stuff. Assuming I have a table name myData in database it would be this simple.
select SomeValue,
(
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('')
)
from myData d
group by d.SomeValue
Hardly any folderol in there. Sure you could code it so it won't have more than 5 columns but honestly once you get past the tally table this is awfully simple.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2014 at 1:06 pm
ScottPletcher (4/29/2014)
For only 5 chars, I wouldn't bother will all the CTEs and related folderol.
You know as well as all of us that requirements do change (par definition), are you going to revisit the code every time a digit or character is added?
😎
April 29, 2014 at 1:22 pm
Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the known 5 bytes somehow expands beyond 20 bytes.
Using XML on five bytes sure seems like folderol to me.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 29, 2014 at 1:28 pm
ScottPletcher (4/29/2014)
Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the known 5 bytes somehow expands beyond 20 bytes.Using XML on five bytes sure seems like folderol to me.
😀 Oh how I miss living in a distant village where changes only happen once every hour:-D
April 29, 2014 at 1:34 pm
Eirikur Eiriksson (4/29/2014)
ScottPletcher (4/29/2014)
Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the known 5 bytes somehow expands beyond 20 bytes.Using XML on five bytes sure seems like folderol to me.
😀 Oh how I miss living in a distant village where changes only happen once every hour:-D
Simple problems sometimes call for simple solutions. Why over-engineer everything?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply