Well that is odd -
I don't know why 2n+1, but at least I get consistent results:
tsm: LFTSM>select (substr(filespace_name,1,10)) as FS1, char(filespace_name,10)
as FS2, cast(filespace_name as char(10)) as FS3 from filespaces
FS1 FS2 FS3
--------------------- --------------------- ---------------------
\\lftsm\c$ \\lftsm\c$ \\lftsm\c$
/depot /depot /depot
/ / /
/sapmnt /sapmnt /sapmnt
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Neil Schofield
Sent: Wednesday, April 17, 2013 6:25 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: [ADSM-L] SELECT statements and column widths
Wanda
> You can also do:
> select substr(filespace_name,1,20) from filespaces
>
> and get only 20 chars back.
Well that's interesting because it makes the problem worse! Using either CAST
or SUBSTR truncate my data to N characters, but CAST gives me a column width
of (N+1) for NODE_NAME and (2N+1) for FILESPACE_NAME, whereas SUBSTR gives me a
column width of (2N+1) for both NODE_NAME and FILESPACE_NAME.
What I was actually aiming for was a column width (ie the number of '-'
characters that underline the column heading) of only N+1 for both
FILESPACE_NAME and NODE_NAME.
select substr(filespace_name,1,20) as FS, substr(filespace_name,1,10) as Node
from filespaces
FS NODE
----------------------------------------- ---------------------
\\xxxxxxxxxx\w$ xxxx
xxxxxxxxxx\SystemSta xxxxxxxxxx
ASR xxxxxxxxxx
\\xxxxxxxxxx\c$ xxxxxxxxxx
\\xxxxxxxxxx\d$ xxxxxxxxxx
\\xxxxxxxxxx\e$ xxxxxxxxxx
\\xxxxxxxxxx\f$ xxxxxxxxxx
\\xxxxxxxxxx\g$ xxxxxxxxxx
xxxxxxx\xxxx\meta\00 xxxxxxxxxx
...
tsm: xxxxxxx>select cast(filespace_name as char(20)) as FS, cast(node_name as
char(10)) as Node from filespaces
FS NODE))
----------------------------------------- -----------
\\xxxxxxxxxx\w$ xxxx
xxxxxxxxxx\SystemSta xxxxxxxxxx
ASR xxxxxxxxxx
\\xxxxxxxxxx\c$ xxxxxxxxxx
\\xxxxxxxxxx\d$ xxxxxxxxxx
\\xxxxxxxxxx\e$ xxxxxxxxxx
\\xxxxxxxxxx\f$ xxxxxxxxxx
\\xxxxxxxxxx\g$ xxxxxxxxxx
xxxxxxx\xxxx\meta\00 xxxxxxxxxx
...
Regards
Neil
----------------------------------------
Spotted a leak?
If you spot a leak please report it immediately. Call us on 0800 57 3553 or go
to http://www.yorkshirewater.com/leaks
Get a free water saving pack
Don't forget to request your free water and energy saving pack, it could save
you money on your utility bills and help you conserve water.
http://www.yorkshirewater.com/savewater
The information in this e-mail is confidential and may also be legally
privileged. The contents are intended for recipient only and are subject to the
legal notice available at http://www.keldagroup.com/email.htm
Yorkshire Water Services Limited
Registered Office Western House, Halifax Road, Bradford, BD6 2SZ Registered in
England and Wales No 2366682
|
