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

convert binary string to table value construct Expand / Collapse
Author
Message
Posted Friday, March 21, 2014 2:46 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
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
Post #1553672
Posted Friday, March 21, 2014 2:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1553674
Posted Friday, March 21, 2014 3:02 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
ok this got interesting so i did the whole thing.
enjoy!
/*
val Skills
00001001001 1,8,64
010111111 1,2,4,8,16,32,128
10101 1,4,16
1010100 4,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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1553675
Posted Friday, March 21, 2014 3:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 3,358, Visits: 7,265
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).



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1553677
Posted Friday, March 21, 2014 3:14 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
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
Post #1553680
Posted Friday, March 21, 2014 3:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
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'

Post #1553688
Posted Friday, March 21, 2014 3:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:36 PM
Points: 541, Visits: 1,044
Yeah, I'm an idiot, Lowell.. It's all there.. lol

Thanks again!
Post #1553691
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse