asked
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')+','+ HASHBYTES('MD5',ISNULL(wl.Password,'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.
You are trying to build a string that contains the whole insert query, so you need to convert the binary into a string using convert(varchar(max),HASHBYTES('MD5',ISNULL(w1.Password,'NULL')),1) so the whole query is SELECT 'INSERT INTO Table2(Username,password)values('+ IsNull(''''+w1.UserName+'''', 'NULL')+','+ convert(varchar(max),HASHBYTES('MD5',ISNULL(w1.Password,'NULL')),1)+')' Alternatively, don't use dynamic sql: INSERT INTO Table2(Username,password) select IsNull(w1.UserName, 'NULL'), HASHBYTES('MD5',ISNULL(Password,'NULL')) from w1
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**
take the isnull outside of the HASHBYTES function, as isnull(convert(varchar(max),HASHBYTES('MD5',Password),1),'NULL') This will change any null-valued passwords to the sting 'NULL' - is that what you want?
13 People are following this question.
