I have a database in which one of its columns is of type of data VARBINARY (MAX) and at the moment of making the query it brings me the data thus
0x7D7D7D7E7E80818182828384848483828281817F7F7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7
in a single column a single record.
There is some way to pass this single record and divide it in such a way that it stays this way
128
129
129
130
130
131
132
138
138
137
137
136
136
135
135
.................... but all doing it with sql
Here I pass a code that can serve you ..
declare @Trama varchar(max) = '0x7D7D7D7E7E80818182828384848483828281817F7F7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7D7'
declare @NumeroIndividual varchar(2) = ''
IF OBJECT_ID ('TEMPDB.DBO.#TramaSeparada') IS NOT NULL
DROP TABLE #TramaSeparada
CREATE TABLE #TramaSeparada
(
ValorNumerico int
)
SET @Trama = Substring(@trama,3,len(@trama))
WHILE LEN(@Trama) > 0
BEGIN
begin
SET @NumeroIndividual = Left(@Trama, 2)
Insert into #TramaSeparada select CAST( CONVERT(VARBINARY,'0x'+RIGHT('00000000'+REPLACE(@NumeroIndividual,'x',''),8),1) AS INT)
SET @Trama = Substring(@trama,3,len(@trama))
END
END
select * from #TramaSeparada
That gives you something like this:
It does not make sense to convert it to a divided string. Who understands a binary? is more useful in binary, in short, there goes the recipe
First convert it to string and from there make a cycle for every 3 characters and add the enter 'char (10)'
something like that
declare @b varbinary(max)
set @b = 0x5468697320697320612074657374A1
SELECT CONVERT(VARCHAR(MAX), @b, 1)
declare @letext varchar(MAX)
set @letext = CONVERT(VARCHAR(MAX), @b, 1)
declare @length int
declare @parsedstring varchar(MAX)
declare @token varchar(3)
declare @startpoint int
set @length = len(@letext)
set @startpoint = 1
while(@length > 3)
begin
set @token = substring(@letext, @startpoint, 3)
set @parsedstring = coalesce(@parsedstring + '|' + @token, @token)
set @startpoint = @startpoint + 3
set @length = @length - 3
end
set @token = substring(@letext, @startpoint, 3)
set @parsedstring = @parsedstring + '|' + @token
select @parsedstring
It's enough that you pass it to a sql function and it's done