SQL Server Performance

convert varchar to varbinary

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Dec 20, 2009.

  1. shankbond New Member

    hi,
    is it possible to convert a varchar(8000) to varbinary(max)?
    I cannot use Convert and Cast functions, but is there some different way I can convert it
    Any help shall be appreciated
  2. FrankKalis Moderator

    Just out of curiosity: Why can't you use CAST or CONVERT?
  3. shankbond New Member

    Cast and Convert functions both of them fail to do so, they cannot convert varchar to varbinary(max)
  4. FrankKalis Moderator

    [quote user="shankbond"]
    Cast and Convert functions both of them fail to do so, they cannot convert varchar to varbinary(max)
    [/quote]
    CREATE TABLE #tmp (c1 varbinary(MAX))

    DECLARE @s varchar(8000);
    SET @s = REPLICATE('ab', 4000);

    INSERT INTO #tmp SELECT CONVERT(varbinary(max), @s);

    SELECT
    *, CONVERT(varchar(8000), c1), DATALENGTH(c1)
    FROM
    #tmp T;


    DROP TABLE #tmp;
  5. shankbond New Member

    Hi Frank,thanks For the support, actually there was a problem some where else, I thank You for the script You gave me ,which helped me understand that CONVERT function can even Convert Varchar to Varbinary.Actually I saw a link on msdn which made me confuse( or may be misunderstood) that Varchar cannot be converted to Varbinaryhttp://msdn.microsoft.com/en-us/library/ms191530.aspx
  6. satya Moderator

    Converting binary and varbinary Data

    When data is converted from a string data type (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext, or image) to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.
    Converting data to the binary and varbinary data types is useful if binary data is the easiest way to move data around. Converting any value of any type to a binary value of large enough size and then back to the type, will always result in the same value if both conversions are taking place on the same version of SQL Server. The binary representation of a value might change from version to version of SQL Server.
    You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. For example, the following SELECT statement shows that the integer value 123456 is usually stored as a binary 0x0001e240:
    from that link you copied, it allows and see the highlighted text whihc is allowed.

Share This Page