Right justify char column

How do I get the output of a SELECT statement to right justify a char (datat
ype) column?
Thanks,
JeffCan't you do that at the application / presentation layer?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff C." <anonymous@.discussions.microsoft.com> wrote in message
news:E7E61AC1-A8B4-4E43-B502-0B794F564A65@.microsoft.com...
> How do I get the output of a SELECT statement to right justify a char
(datatype) column?
> Thanks,
> Jeff|||Jeff C. wrote:

> How do I get the output of a SELECT statement to right justify a char
> (datatype) column?
Taken from
http://groups.google.pl/groups?hl=p...&rnu
m=3
Declare @.InVal Char(38)
Declare @.ReplaceChar Char(1)
Select @.InVal = 'This is a test'
Select @.ReplaceChar ='x'
Select Replicate(@.ReplaceChar, 38 - Len(@.InVal)) + @.InVal
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

right blank space in sql

I need ti getthe length of a string like this (varchar or char doesn't matte
r)
'AB12345678A '. The length is 14, but the function len('AB12345678A ')
return 11. Len doesn't count the blank spaces. How con i get yhe length of m
y
strings including blank spaces?Hi
It depends on the ANSI_PADDING setting for you DB, but LEN will always
return the number of characters, right trimmed.
If ANSI_PADDING is ON when the data it put into the DB, you could use this
work around:
SELECT LEN('AB12345678A ' + '.') -1
This will cause a "." (or any other character) to be appended after the
blanks, so LEN will not trim it, then subtract one from the total count to
compensate for the "."
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:F8C0249C-6E56-465A-995E-A0AF0701F8F8@.microsoft.com...
> I need ti getthe length of a string like this (varchar or char doesn't
matter)
> 'AB12345678A '. The length is 14, but the function len('AB12345678A ')
> return 11. Len doesn't count the blank spaces. How con i get yhe length of
my
> strings including blank spaces?|||Hi,
I have used Datalength fucntion it gives me value 14.
Read BOL for Datalength and Len function
Thanks
AM
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%233v1nu5KFHA.2796@.tk2msftngp13.phx.gbl...
> Hi
> It depends on the ANSI_PADDING setting for you DB, but LEN will always
> return the number of characters, right trimmed.
> If ANSI_PADDING is ON when the data it put into the DB, you could use this
> work around:
> SELECT LEN('AB12345678A ' + '.') -1
> This will cause a "." (or any other character) to be appended after the
> blanks, so LEN will not trim it, then subtract one from the total count to
> compensate for the "."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:F8C0249C-6E56-465A-995E-A0AF0701F8F8@.microsoft.com...
> matter)
')
of
> my
>|||"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:F8C0249C-6E56-465A-995E-A0AF0701F8F8@.microsoft.com...
>I need ti getthe length of a string like this (varchar or char doesn't
>matter)
> 'AB12345678A '. The length is 14, but the function len('AB12345678A ')
> return 11. Len doesn't count the blank spaces. How con i get yhe length of
> my
> strings including blank spaces?
Try DATALENGTH instead.
SELECT LEN('AB12345678A ') returns 11
SELECT DATALENGTH('AB12345678A ') returns 14
Dan

Returning Text String.

I am returning a rather large result set using a basic select query.
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?You weren't very specific. Perhaps:
select
case when MyCol = 'c' then 'contractor'
else 'something else'
end
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:B8B7C546-3708-4326-8311-580CF570FF62@.microsoft.com...
I am returning a rather large result set using a basic select query.
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?|||Another way would be to have all of these values in a lookup table and join
to it.
E.g.
Lookup Value, Lookup Name
C, Contractor
Then join to this table on Lookup Value but return Lookup Name in your
select statement
select col1, col2, LookupName
from table
inner join LookupTable
on table.lookupvalue = lookuptable.lookupValue
Hope this helps
Clint Colefax|||Yes you can use a CASE WHEN statement to return the data in the format you
want it. Below is an example of how to do this with your sinaro.
Create TABLE #TEMP
(
EMPTYPE nvarchar (1),
empNAME nvarchar (5)
)
Insert #temp
values ( 'c','Dave')
Insert #temp
values ( 'f','Joe')
Insert #temp
values ( 'p','Rick')
Insert #temp
values ( 'c','Bo')
Select CASE WHEN emptype ='c'
THEN 'Contractor'
WHEN emptype = 'f'
THEN 'FULLTIME'
WHEN emptype = 'p'
THEN 'PARTTIME'
END AS EMPLOYEE_TYPE,
EMPNAME
from #temp
dROP TABLE #TEMP
--
Please refer to books online for more indepth look at how to use CASE
statement.
Hope this helps
JEP
"AJ" wrote:

> I am returning a rather large result set using a basic select query.
> One of the columns being returned is a char(1) column.
> I am wondering if their is a way in sql to return an associated text strin
g
> to a char(1) column.
> Eg:
> If char(1) = 'c' Then text string returned in place place of 'c' should b
e
> contractor.
> I am sure i could possibly do this using an if statement on the result
> set..of some kind.
> Can it be done using any other method?

Returning Text + Column

Dear All
CREATE TABLE [dbo].[Tester] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
insert into Tester (TesterText) Values ('AAAA')
insert into Tester (TesterText) Values ('BBBB')
insert into Tester (TesterText) Values ('CCCC')
insert into Tester (TesterText) Values ('DDDD')
Given the above Schema and insert I would like to return a varchar with text
+ the column TesterText, so in this case I would like a varchar with 'Hello
AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
I would rather not use cursors.
TIA
JJulie
Why not doing shuch things on the client side
I did not check NULLs and for another world it does not work as well
DECLARE @.v VARCHAR(100)
SET @.v=''
SELECT @.v=@.v+''+TesterText+';'+'Hello' FROM Tester
SELECT REPLACE(LEFT(@.v,LEN(@.v)-6),' ','')+';'
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:3C344282-FD6C-46DC-B051-1830200B247B@.microsoft.com...
> Dear All
> CREATE TABLE [dbo].[Tester] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
> CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> insert into Tester (TesterText) Values ('AAAA')
> insert into Tester (TesterText) Values ('BBBB')
> insert into Tester (TesterText) Values ('CCCC')
> insert into Tester (TesterText) Values ('DDDD')
> Given the above Schema and insert I would like to return a varchar with
text
> + the column TesterText, so in this case I would like a varchar with
'Hello
> AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
> I would rather not use cursors.
> TIA
> J
>|||The best place for this kind of non-relational concatenation is on the
client, not in the database. However, see http://www.aspfaq.com/2529 for a
starting point if you really, really, really want to do this in the
database.
On 3/15/05 6:05 AM, in article
3C344282-FD6C-46DC-B051-1830200B247B@.microsoft.com, "Julie"
<Julie@.discussions.microsoft.com> wrote:

> Dear All
> CREATE TABLE [dbo].[Tester] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
> CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> insert into Tester (TesterText) Values ('AAAA')
> insert into Tester (TesterText) Values ('BBBB')
> insert into Tester (TesterText) Values ('CCCC')
> insert into Tester (TesterText) Values ('DDDD')
> Given the above Schema and insert I would like to return a varchar with te
xt
> + the column TesterText, so in this case I would like a varchar with 'Hell
o
> AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
> I would rather not use cursors.
> TIA
> J
>|||Thanks Guys,
I have given Uri the 'answered' as he was the first :D
Anyway what I'm actually doing is preparing a number of Dynamic SQL
Statments for a nightly batch run (we have the column + table names in a
table) however I couldn't find a way of concatinating them together.
Anyway thanks for that
J
"Julie" wrote:

> Dear All
> CREATE TABLE [dbo].[Tester] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
> CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> insert into Tester (TesterText) Values ('AAAA')
> insert into Tester (TesterText) Values ('BBBB')
> insert into Tester (TesterText) Values ('CCCC')
> insert into Tester (TesterText) Values ('DDDD')
> Given the above Schema and insert I would like to return a varchar with te
xt
> + the column TesterText, so in this case I would like a varchar with 'Hell
o
> AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
> I would rather not use cursors.
> TIA
> J
>