problem with conversion of binary to varchar

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

SQL Server 2000 Forums

Transact-SQL (2000)

problem with conversion of binary to varchar

Author

Topic

saibrvvarma
Starting Member

2 Posts

Posted - 2007-08-02 : 19:52:48

Hi,
We are having a database which consists of user name and password which is used by a .net application

the problem is when we are retreiving the passwords by converting binary to character like this convert(varchar(10),passwordfield)

we are getting the results good but the problem is this instead of returning the actual results its appending some special characters.

For example the password field is binary(10) and we store password as welcome1

but when we retreive the length of the password it shows as 10 even after using rtrim and ltrim

if we see from enterprise manager its showing 2 extra characters.Its breaking up the whole application in production..

Please help.....

Thanks
vijay

vijay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 03:29:34

Use VARBINARY

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 03:54:42

[code]CREATE FUNCTION dbo.fnBIN2VC
(
@Value BINARY(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Index INT,
@CHAR CHAR(1),
@Result VARCHAR(8000),
@Len INT

SELECT @Index = 1,
@Result = '',
@Len = LEN(@Value)

WHILE @Index <= @Len
BEGIN
SET @CHAR = SUBSTRING(@Value, @Index, 1)
IF @CHAR = CHAR(0)
BREAK

SELECT @Index = @Index + 1,
@Result = @Result + @CHAR
END

RETURN @Result
END[/code]Use this test code[code]DECLARE @a BINARY(20)

SET @a = CAST('Peso' AS BINARY(20))

select @a,
dbo.fnBIN2VC(@a)[/code]

E 12°55'05.25"
N 56°04'39.16"

saibrvvarma
Starting Member

2 Posts

Posted - 2007-08-03 : 09:01:42

Thanks Peso, your solution, has resolved my issue.

vijay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources