The difference is not in char vs substr vs cast, but in filespace_name vs node_name:
tsm: LFTSM>select (substr(filespace_name,1,10)) as FS1, char(node_name,10) as NN1, cast(filespace_name as char(10)) as FS3 from filespaces FS1 NN1 FS3 --------------------- ----------- --------------------- \\lftsm\c$ LFTSM \\lftsm\c$ LFTSM\Syst LFTSM LFTSM\Syst /depot LFUSABWP /depot /usr/sap/B LFUSABWP /usr/sap/B They are both VARCHAR. So I have no explanation. At all. -----Original Message----- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Prather, Wanda Sent: Thursday, April 18, 2013 12:25 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] SELECT statements and column widths 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@VM.MARIST.EDU] On Behalf Of Neil Schofield Sent: Wednesday, April 17, 2013 6:25 PM To: ADSM-L@VM.MARIST.EDU<mailto:ADSM-L@VM.MARIST.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
