Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypted value shown as '?' in a column of type varchar

Dear All,

I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '?' instead of showing the encrypted value that I sent with the insert statement.3

............................ Can anyone tell me how to get rid of this?

Thanks and regards,

Z Z.

How are you encrypting the data and how are you retrieving it?|||Thanks for your reply. Actually I'm using only one-way encryption and seen these '?' through SQL Server Management Studio by directly viewing the table contents.|||So what are you expecting to see returned if you are using one way encryption?|||

I was expecting to see encrypted value when I opened the database table directly from within the SQL Server Management Studio. Instead I found '?' only. Anyway, I am done with it and used two encryption mechanism. Thanks a lot.

Encrypted data size by original size, algorithm ?

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.

Thanks
Laurentiu

|||

Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:

http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx

Thanks
Laurentiu

Encrypted data size by original size, algorithm ?

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.

Thanks
Laurentiu

|||

Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:

http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx

Thanks
Laurentiu

encrypt a text or varchar(max) field

I understood that sql 2005 has EncryptByCert(varchar) function to encrypt data field. but varchar is limit by 8,000 chars long.
My question is:
as sql 2005 built in function, is there any way to encrypt a text or varchar(max) fieild which could be more than 8,000 chars?
Thanks
duohong

It is not recommended to encrypt data directly using certificates; instead, it is recommend to use symmetric keys to encrypt data, and certificates can be used to encrypt those symmetric keys. One reason for this is that asymmetric key encryption is much slower than symmetric key encryption. Also, asymmetric key algorithms are particularly good for key management, but are not well suited for general data encryption, which is why asymmetric and symmetric keys are in practice used together, the first for allowing secure key exchange and the second for the actual data encryption. In SQL Server 2005, the main purpose of certificates is for signing modules and for encrypting other keys, not for directly encrypting data.

Also, note that certificates are currently generated with 1024 bit length private keys and the length of data that you can encrypt with such a certificate is at most 117 bytes (1024/8 - 11 for padding). This is less than the varchar limit of 8000 characters, but is sufficient for the intended use of certificates.

Thanks
Laurentiu

Encoding For HashBytes

When SQL Server attempts to do a MD5 hash on this string it most encode the
string to binary before hashing it. Does anyone know how varchar is encoded
,
i.e. what encoding is used for varchar? UTF-8? UTF-16? Example code:
SELECT HashBytes('MD5',CONVERT(varchar,’some string’))
If you send a nvarchar, it uses UTF-16, example:
SELECT HashBytes('MD5',CONVERT(nvarchar,’some
string’))
Thanks in advance. With this information I can write some C# code to create
a hash that matches what SQL server does.
-WayneWayne Berry (WayneBerry@.discussions.microsoft.com) writes:
> When SQL Server attempts to do a MD5 hash on this string it most encode
> the string to binary before hashing it. Does anyone know how varchar is
> encoded, i.e. what encoding is used for varchar? UTF-8? UTF-16?
> Example code:
> SELECT HashBytes('MD5',CONVERT(varchar,some string))
> If you send a nvarchar, it uses UTF-16, example:
> SELECT HashBytes('MD5',CONVERT(nvarchar,some string))
> Thanks in advance. With this information I can write some C# code to
> create a hash that matches what SQL server does.
I would suspect that it simply hashes the byte value. Which for varchar
means codes in the range 0 to 255(*), and for nvarchar a UTF-16 encoding.
(*) For Western scripts. For East Asian scripts it would be a double-
byte character set.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>
|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
Anith
|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
Anith
|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
http://www.aspfaq.com/
(Reverse address to reply.)
|||I believe the OP said it was an empty string?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>
|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
>
|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>
|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>
|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>

Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
--
Anith|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
--
Anith|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I believe the OP said it was an empty string?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> >> Can you post code to reproduce your problem?
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> > >> Can you post code to reproduce your problem?
> >
> > INSERT @.table SELECT SPACE(1) ;
> >
> > --
> > Anith
> >
> >
>|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> > MyCol is a varchar(1) column.
> >
> > SELECT MyCol + '.' FROM tbl
> >
> > returns ' .' (space+dot), when MyCol contains empty string.
> > Why? How can I make it to return '.' (with no space)?
> >
> > I use SQL Server 2000, default settings.
> >
> > Thanks.
> >
> >
>|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
> First of all, why are you using varchar(1)? You're wasting an extra byte
> per row for nothing... Use CHAR(1).
>
Originally MyCol was Char(1) and contained Nulls. Then it appeared that this
column should be used in a join, so I had to get rid of Nulls.
I also have a few Web pages (in ASP.NET) built in assumption that MyCol is
not blank. So, I decided to use Varchar(1) and an empty string for MyCol
instead of using a space and updating the queries or code with a Trim
function.
> Second, how are you determining that the output is ' .'? Is this
happening
> client-side? I cannot reproduce what you're talking about, using the
> following:
>
> declare @.table table(blah varchar(1))
> insert @.table values ('')
> select len(blah + '.')
> from @.table
>
> Can you post code to reproduce your problem?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> > MyCol is a varchar(1) column.
> >
> > SELECT MyCol + '.' FROM tbl
> >
> > returns ' .' (space+dot), when MyCol contains empty string.
> > Why? How can I make it to return '.' (with no space)?
> >
> > I use SQL Server 2000, default settings.
> >
> > Thanks.
> >
> >
>|||Is your database set to a compatibility level of 65, perhaps? SQL
Server 6.5 could not store an empty string, if I recall correctly.
Otherwise, how do you know MyCol is empty? This behavior will result if
MyCol contains the value ' '. You can try rtrim(MyCol) to trim any
trailing spaces, but it would be best to find out what is going on.
Steve Kass
Drew University
Vik wrote:
>MyCol is a varchar(1) column.
>SELECT MyCol + '.' FROM tbl
>returns ' .' (space+dot), when MyCol contains empty string.
>Why? How can I make it to return '.' (with no space)?
>I use SQL Server 2000, default settings.
>Thanks.
>
>|||Vik wrote:
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one
> space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns
> 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>> MyCol is a varchar(1) column.
>> SELECT MyCol + '.' FROM tbl
>> returns ' .' (space+dot), when MyCol contains empty string.
>> Why? How can I make it to return '.' (with no space)?
>> I use SQL Server 2000, default settings.
>> Thanks.
Use DATALENGTH() for the real stored length.
create table #testing(col1 varchar(1))
insert into #testing values (space(1))
select len(col1) as 'len', datalength(col1) as 'datalength'
from #testing
len datalength
-- --
0 1
David Gugick
Imceda Software
www.imceda.com

Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
Anith|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
Anith|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
http://www.aspfaq.com/
(Reverse address to reply.)|||I believe the OP said it was an empty string?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
>|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>

empty status of a variable

The example bellow will receive a parameter.

create procedure usp_InsertProducts
@.SKU varchar(30)

Now how do I check whether is @.SKU empty or not.

Your help is kindly appreciated.

Regards

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***> how do I check whether is @.SKU empty or not.

IF @.sku IS NULL ...

or

IF @.sku = '' ...

depending on what you mean by "empty".

To differentiate between a NULL and no value specified you could
provide a default using some invalid token to represent the missing
value:

CREATE PROCEDURE usp_InsertProducts
@.sku VARCHAR(30) = '<Unspecified>'
AS ...

IF @.sku = '<Unspecified>'
...

--
David Portas
SQL Server MVP
--