Datalength of unicode and non-unicode types?

I'm using SQL Server 2000. Suppose I'm in Northwind database and
I execute the following query:
SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
The results of 'Text length' shows 2 X total characters because
Notes is of type ntext (Unicode type).
Q: How to form a query that show the total characters used(in this case
notes/2) provided I'm not sure about the underlying datatype whether
it's Unicode or otherwise?
How to check underlying datatype of a column using T-SQL programmatically?
Regards,
Pedestrian
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1If you know the column type is some string type, you
can try this. I'm guessing that the 0-length substring
calculation will be relatively painless:
SELECT
Notes,
Datalength(Notes)
/CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
THEN 2 ELSE 1 END
FROM Employees
Steve Kass
Drew University
pedestrian via webservertalk.com wrote:

>I'm using SQL Server 2000. Suppose I'm in Northwind database and
>I execute the following query:
>SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
>The results of 'Text length' shows 2 X total characters because
>Notes is of type ntext (Unicode type).
>Q: How to form a query that show the total characters used(in this case
>notes/2) provided I'm not sure about the underlying datatype whether
>it's Unicode or otherwise?
>How to check underlying datatype of a column using T-SQL programmatically?
>Regards,
>Pedestrian
>
>|||If you just want to count number of characters, use len instead of
datalength. This works for both Unicode and non-Unicode strings.
You can query column information in T-SQL by using the
INFORMATION_SCHEMA.COLUMNS view,
HTH
- Baileys
pedestrian via webservertalk.com wrote:
> I'm using SQL Server 2000. Suppose I'm in Northwind database and
> I execute the following query:
> SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
> The results of 'Text length' shows 2 X total characters because
> Notes is of type ntext (Unicode type).
> Q: How to form a query that show the total characters used(in this case
> notes/2) provided I'm not sure about the underlying datatype whether
> it's Unicode or otherwise?
> How to check underlying datatype of a column using T-SQL programmatically?
> Regards,
> Pedestrian
>|||Baileys wrote:
> If you just want to count number of characters, use len instead of
> datalength. This works for both Unicode and non-Unicode strings.
However, you need to keep in mind that LEN() excludes the trailing
blanks when counting the number of characters (but DATALENGTH() does
not).
Razvan|||Steve Kass wrote:
> If you know the column type is some string type, you
> can try this. I'm guessing that the 0-length substring
> calculation will be relatively painless:
> SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
> FROM Employees
Hello, Steve
That's a brilliant trick.
However, I'm not sure why you have used the CASE expression ?
Consider this query:
SELECT
Notes,
Datalength(Notes)
/ DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
)
FROM Employees
Wouldn't this be the same as your query ?
Razvan|||
Razvan Socol wrote:

>Steve Kass wrote:
>
>Hello, Steve
>That's a brilliant trick.
>However, I'm not sure why you have used the CASE expression ?
>Consider this query:
>SELECT
> Notes,
> Datalength(Notes)
> / DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
)
>FROM Employees
>Wouldn't this be the same as your query ?
>Razvan
>
>
Yup. Good catch.
SK|||
Baileys wrote:

> If you just want to count number of characters, use len instead of
> datalength. This works for both Unicode and non-Unicode strings.
But LEN does not accept types ntext and text, which the user required.
SK
> You can query column information in T-SQL by using the
> INFORMATION_SCHEMA.COLUMNS view,
> HTH
> - Baileys
> pedestrian via webservertalk.com wrote:
>|||oops, I missed that part of the question...
- Baileys
Steve Kass wrote:
>
> Baileys wrote:
>
>
> But LEN does not accept types ntext and text, which the user required.
> SK
>|||Thanks for quick replies.... particularly to Steve Kass 'n Razvan Socol ...
.
Best Regards,
Pedestrian
Steve Kass wrote:
>If you know the column type is some string type, you
>can try this. I'm guessing that the 0-length substring
>calculation will be relatively painless:
>SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
>FROM Employees
>Steve Kass
>Drew University
>
>[quoted text clipped - 14 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Confused over the following query which return the result 2s:
SELECT Datalength(space(1)+SUBSTRING(Notes,1,0)
) as myCol from employees
Why not the above query return 1s instead of 2s ... ?
I suppose space(1) returns 1 and SUBSTRING(Notes,1,0) as below return 0 as
below...
hence space(1)+SUBSTRING(Notes,1,0) should only returns 1.
This query return me 1... Ok
SELECT Datalength(SPACE(1)) As Slength
This query return me 0s... No problem...
SELECT Datalength(SUBSTRING(Notes,1,0) ) As Length1 FROM Employees
Steve Kass wrote:
>If you know the column type is some string type, you
>can try this. I'm guessing that the 0-length substring
>calculation will be relatively painless:
>SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
>FROM Employees
>Steve Kass
>Drew University
>
>[quoted text clipped - 14 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Databasepropertyex problem

Hi
when I run this query
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=test;UID=sa;PWD=amish;','
select name,
databasepropertyex(name,''status'') as status from
master..sysdatabases') AS a
Result is
Name Status
master 0x4F004E004C0049004E004500
tempdb 0x4F004E004C0049004E004500
model 0x4F004E004C0049004E004500
msdb 0x4F004E004C0049004E004500
test1 0x5300550053005000450043005400
northwind 0x4F004E004C0049004E004500
If we run select databasepropertyex('dbname','status') it gives result
as character , why here it gives varbinary results in status column?
Regards
Amish Shah
Regards
Amish shah
*** Sent via Developersdex http://www.examnotes.net ***Databasepropertyex returns an sql_variant, CAST it to the proper datatype (d
epending on what you ask
for, and I think you should be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:O65DZCKNGHA.3064@.TK2MSFTNGP10.phx
.gbl...
> Hi
> when I run this query
> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=test;UID=sa;PWD=amish;','
select name,
> databasepropertyex(name,''status'') as status from
> master..sysdatabases') AS a
> Result is
> Name Status
> master 0x4F004E004C0049004E004500
> tempdb 0x4F004E004C0049004E004500
> model 0x4F004E004C0049004E004500
> msdb 0x4F004E004C0049004E004500
> test1 0x5300550053005000450043005400
> northwind 0x4F004E004C0049004E004500
> If we run select databasepropertyex('dbname','status') it gives result
> as character , why here it gives varbinary results in status column?
> Regards
> Amish Shah
> Regards
> Amish shah
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Amish Shah (shahamishm@.gmail.com) writes:
> when I run this query
> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=test;UID=sa;PWD=amish;','
select name,
> databasepropertyex(name,''status'') as status from
> master..sysdatabases') AS a
> Result is
> Name Status
> master 0x4F004E004C0049004E004500
> tempdb 0x4F004E004C0049004E004500
> model 0x4F004E004C0049004E004500
> msdb 0x4F004E004C0049004E004500
> test1 0x5300550053005000450043005400
> northwind 0x4F004E004C0049004E004500
> If we run select databasepropertyex('dbname','status') it gives result
> as character , why here it gives varbinary results in status column?
Any particular reason you use MSDASQL? When I use SQLOLEDB, I get back
character data without cast.
SELECT * FROM OPENROWSET('SQLOLEDB', 'SERVER=test;UID=sa;PWD=xxxxx;',
'select name, databasepropertyex(name,''status'') as status from
master..sysdatabases') AS a
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

Databasepropertyex problem

Hi
when I run this query
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=test;UID=sa;PWD=amish;','
select name,
databasepropertyex(name,''status'') as status from
master..sysdatabases') AS a
Result is
Name Status
master 0x4F004E004C0049004E004500
tempdb 0x4F004E004C0049004E004500
model 0x4F004E004C0049004E004500
msdb 0x4F004E004C0049004E004500
test1 0x5300550053005000450043005400
northwind 0x4F004E004C0049004E004500
If we run select databasepropertyex('dbname','status') it gives result
as character , why here it gives varbinary results in status column?
Regards
Amish Shah
Regards
Amish shah
*** Sent via Developersdex http://www.examnotes.net ***Amish Shah wrote:
> Hi
> when I run this query
> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=test;UID=sa;PWD=amish;','
select name,
> databasepropertyex(name,''status'') as status from
> master..sysdatabases') AS a
> Result is
> Name Status
> master 0x4F004E004C0049004E004500
> tempdb 0x4F004E004C0049004E004500
> model 0x4F004E004C0049004E004500
> msdb 0x4F004E004C0049004E004500
> test1 0x5300550053005000450043005400
> northwind 0x4F004E004C0049004E004500
> If we run select databasepropertyex('dbname','status') it gives result
> as character , why here it gives varbinary results in status column?
> Regards
> Amish Shah
> Regards
> Amish shah
>
Good question, but you can cast is back:
SELECT * FROM OPENROWSET(
'MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=my_pas
s;','select name,
CAST(databasepropertyex(name,''status'')
as NVARCHAR(100)) as status
from master..sysdatabases') AS a
David Gugick - SQL Server MVP
Quest Software