Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Convert BINARY to VARCHAR

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-30 : 07:55:05
Jeffrey writes "Interest in converting BINARY to VARCHAR in MS SQL.

/* CONCEPT IS TO TRANSLATE THIS SQL STATEMENTS INTO SPROC. HOWEVER INTERESTED IN SHOWING BEFORE AND AFTER OF CONVERTING NVARCHAR TO BINARY AND THEN BINARY BACK TO NVARCHAR. NEED TO DETERMINE BEST METHOD TO CONVERT BINARY BACK TO NVARCHAR.
*/
DECLARE @staff_sys INT,@uid nvarchar(15),@password nvarchar(30),@new_password BINARY(20),@active INT
-- the uid and password will be provided
SET @uid='test'
SET @password='ChangeMePlease' -- enter desired password here

SELECT @staff_sys=staff_sys,@active=active FROM staff NOLOCK WHERE uid LIKE @uid+'%'

IF EXISTS(SELECT * FROM tempdb..sysobjects NOLOCK WHERE name LIKE '#temp_uid_pswd%')
BEGIN
DROP TABLE #temp_uid_pswd
END

CREATE TABLE #temp_staff
(
staff_sys INT, -- unique user identifier
uid nvarchar(15), -- user id name
active INT, -- is user active
old_password BINARY(20), -- current password
new_password BINARY(20) -- new password
)

IF @active<1
BEGIN
PRINT 'The userid: '+@uid+' is not active - contact Systems Administrator - step failed'
END
ELSE
BEGIN

-- SELECT CAST(CAST(password AS nvarchar) AS varbinary) FROM staff NOLOCK WHERE staff_sys=@staff_sys
-- SELECT CAST(CAST(CAST(password AS nvarchar) AS varbinary) AS nvarchar) FROM staff NOLOCK WHERE staff_sys=@staff_sys

INSERT INTO #temp_uid_pswd
SELECT staff_sys,uid,active,password,NULL FROM staff NOLOCK WHERE uid LIKE @uid+'%' AND staff_sys=@staff_sys

SELECT * FROM #temp_uid_pswd

SET @new_password=CONVERT(BINARY(20),@password)

UPDATE staff SET password=@password
WHERE staff_sys=@staff_sys

PRINT 'The userid: '+@uid+' password has been changed successfully to '+@password+' (i.e. case sensitive).'

UPDATE #temp_uid_pswd
SET new_password=@new_password
WHERE uid LIKE @uid+'%' AND staff_sys=@staff_sys

CAST(password AS nvarchar) FROM #temp_uid_pswd
END
DROP TABLE #temp_uid_pswd
ROLLBACK TRAN"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-30 : 10:23:47
Ummmhhh, encrypt the passwords???
try the UNCOMPRESS command (not documented)
SELECT
CONVERT(BINARY(20),'the text to convert') AS bin
,UNCOMPRESS(CONVERT(BINARY(20),'the text to convert')) AS txt


rockmoose
Go to Top of Page
   

- Advertisement -