Convert Password nvarchar() to binary datatype in sql

I want to convert Password from varchar to binary.

I have the query like,

 SELECT 'INSERT INTO Table2(Username,password)values('+
 IsNull(''''+wl.UserName+'''', 'NULL')+','+

But i have error when i execute the above code,

 The data types nvarchar and varbinary are incompatible in the add operator.

I need your help. My query is correct. why it is show like that.

more ▼

asked Apr 23, 2013 at 10:19 AM in Default

avatar image

80 4 6 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You are trying to build a string that contains the whole insert query, so you need to convert the binary into a string using


so the whole query is

 SELECT 'INSERT INTO Table2(Username,password)values('+
 IsNull(''''+w1.UserName+'''', 'NULL')+','+

Alternatively, don't use dynamic sql:

 INSERT INTO Table2(Username,password)
  IsNull(w1.UserName, 'NULL'),
 from w1

more ▼

answered Apr 23, 2013 at 10:35 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Thanks.i want clarification. What is ISNULL(w1.Password,'NULL')),1) Here 1 is there. why use 1 here?

Apr 23, 2013 at 10:40 AM udhaya

It's the style parameter for the convert function. See here : http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx and scroll down to Binary Styles

Apr 23, 2013 at 10:43 AM Kev Riley ♦♦

Actually, ISNULL(w1.Password,'NULL') this query is convert NULL to MD5. so it is show "0x351A64C5DE93984A0B71280CECA8B464" like that where NULL values there. Is this one correct way or not?Please help me.

Apr 23, 2013 at 12:02 PM udhaya

take the isnull outside of the HASHBYTES function, as


This will change any null-valued passwords to the sting 'NULL' - is that what you want?

Apr 23, 2013 at 12:31 PM Kev Riley ♦♦

Thanks. It is working

Apr 24, 2013 at 04:49 AM udhaya
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 23, 2013 at 10:19 AM

Seen: 4851 times

Last Updated: Apr 24, 2013 at 11:57 AM

Copyright 2018 Redgate Software. Privacy Policy