none
Sql query and converting Base64 in table

    Question

  • Hi I am trying to convert or decode a column that is in base64. I have query that can convert one name at a time but what i want is the query to do is convert all the names into a new column. Sorry for being so green at this.

    here is the query that dispalys the names

    SELECT

     

     

    [Location]

     

    ,[User] (this is the one that is in base64!!!)

     

    ,[NetworkID]

     

    ,[Assignment]

     

    ,[Serial]

     

    ,[Date]

     

    FROM [TracerDB].[dbo].[AssetTrack]

     

    I did find this fromt the web that can convert a single name when put in the variable of @data

    DECLARE

     

    @data varchar(max), @XmlData xml

     

     

    -- set the base64 encoded varchar.

    Set

     

    @data = 'TDk5OTM0==' (here is where i want to put in more than just one value)

    -- construct an xml var.

    SET

     

    @XmlData = CAST('<data>' + @data + '</data>' as xml)

    -- base64 decode the @data.

    SELECT

     

    CONVERT(varchar(max),

     

    .value('(data)[1]', 'varbinary(max)'))

     

    Thanks in advance

     

    Carl

     

    @XmlData

     

     

     

     

     

     

     

     

     

     

     

     

    Thursday, February 17, 2011 3:10 PM

Answers

  • You could just wrap that code in a function, or include it inline, eg

    USE tempdb
    GO
    
    SET NOCOUNT ON
    GO
    
    CREATE TABLE #tmp ( yourUser VARCHAR(MAX) )
    GO
    
    INSERT INTO #tmp ( yourUser )
    SELECT 'TDk5OTM0=='
    UNION
    SELECT 'SDk5OTM0=='
    UNION
    SELECT 'SDl5TTM0=='
    GO
    
    -- Generate some dummy data
    INSERT INTO #tmp ( yourUser )
    SELECT LOWER( LEFT( NEWID(), 8 ) ) + '=='
    GO 10
    
    SELECT *,
    	CAST( CAST('<data>' + yourUser + '</data>' AS XML).value('data[1]', 'VARBINARY(MAX)') AS VARCHAR(MAX) ) x
    FROM #tmp
    GO
    
    -- Cleanup
    DROP TABLE #tmp
    GO
    
    
    • Marked as answer by KJian_ Tuesday, March 01, 2011 2:51 AM
    Thursday, February 17, 2011 3:53 PM
    Answerer