March 21, 2014 at 2:46 pm
I'm about to start on this but my search revealed nothing.
Anyone have a code snippet for changing '00001001001' into a table value construct containing (64,8,1)?
Erin
March 21, 2014 at 2:56 pm
Erin i have this saved in my snippets, that will get you 90% there;
this is enumerating the bits as to wether they are true or false.
;WITH myStrings (val)
AS (
SELECT '00001001001' UNION ALL
SELECT '10101' UNION ALL
SELECT '1010100' UNION ALL
SELECT '010111111' )
,
FormattedStrings AS (
SELECT right('0000000000000000000000000000000' + val ,31) As sval
FROM myStrings),
MiniTally AS (
SELECT TOP 31 row_number() OVER (order by name) As N from sys.columns order by name )
select
sval,
MiniTally.N,
CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) As bval,
POWER(2,N-1) As thePower,
CASE
WHEN (CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) & 1) = 0
THEN 'False'
ELSE 'True'
END As [True?]
FROM FormattedStrings
CROSS JOIN MiniTally
order by sval,MiniTally.N
from there, if you filtered it for only true, and used FOR XML to concatenate the [thePower] column, you'd have what you were asking for.
does that help?
i can look at making the concat work if you are a bit weak on that too.
Lowell
March 21, 2014 at 3:02 pm
ok this got interesting so i did the whole thing.
enjoy!
/*
valSkills
000010010011,8,64
0101111111,2,4,8,16,32,128
101011,4,16
10101004,16,64
*/
;WITH myStrings (val)
AS (
SELECT '00001001001' UNION ALL
SELECT '10101' UNION ALL
SELECT '1010100' UNION ALL
SELECT '010111111' )
,
FormattedStrings AS (
SELECT val,
right('0000000000000000000000000000000' + val ,31) As sval
FROM myStrings),
MiniTally AS (
SELECT TOP 31 row_number() OVER (order by name) As N from sys.columns order by name ),
StagedData
AS
(
select
val,
sval,
MiniTally.N,
CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) As bval,
POWER(2,N-1) As thePower,
CASE
WHEN (CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) & 1) = 0
THEN 'False'
ELSE 'True'
END As [True?]
FROM FormattedStrings
CROSS JOIN MiniTally
--order by sval,MiniTally.N
)
SELECT val,stuff(( SELECT ',' + convert(varchar,ThePower)
FROM StagedData s2
WHERE s2.val= s1.val --- must match GROUP BY below
And [True?]='True'
ORDER BY N
FOR XML PATH('')
),1,1,'') as [Skills]
FROM StagedData s1
GROUP BY s1.val --- without GROUP BY multiple rows are returned
ORDER BY s1.val
Lowell
March 21, 2014 at 3:06 pm
Here's another option. I was thinking more of creating it for an iTVF (but leaving that part to you).
DECLARE @String varchar(20) = '00001001001';
WITH e1(N) AS(
SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))e(N)
),
cteTally(N) AS(
SELECT TOP(LEN(@String)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) - 1 FROM e1 a, e1 b
)
SELECT STUFF((SELECT ',' + CAST( POWER(2,N) AS varchar(5))
FROM cteTally
WHERE SUBSTRING( @String, LEN(@String) - N, 1) = 1
ORDER BY N DESC
FOR XML PATH('')), 1, 1, '')
By the way, it was a surprise that ^ is not power in SQL and I had to use the POWER function instead. I haven't used it in a long, long time (if I ever used it before).
March 21, 2014 at 3:14 pm
Thank you both! I found out the same thing about the power() function and was a little surprised but I haven't used it in a while either. I'll try them both out.
Regards,
Erin
March 21, 2014 at 3:41 pm
Well this is interesting..
Hey, Lowell, try using this in your function and change the int to bigint. I must be missing something because it's not returning everything.
'111111101100010000111100000001'
March 21, 2014 at 3:55 pm
Yeah, I'm an idiot, Lowell.. It's all there.. lol
Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply