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
