June 21, 2011 at 4:26 am
Hi
Can someone tell me the syntax to Left Justify a set of results I've tried the Ltrim but that doesn't seem to work
Current Results example
Desired Results
111
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 21, 2011 at 4:46 am
June 21, 2011 at 4:49 am
Adding to that, trim removes spaces, so the characters to the left that you wish to remove must be something else. Step 1 is to find out what those characters are. Then use REPLACE to get rid of them.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 21, 2011 at 5:58 am
Thanks for your replies
To be honest i don't understand the script below, this returns 32 for me.
declare @name varchar(256)
set @name = ' 1231'
select ascii(SUBSTRING(@name , 1 , 1))
I cant get my head round how the above code is meant to work with a replace.
My understanding of the replace is the following how does that relate to the above???
SELECT REPLACE('ReportServices','Report','Analysis_');
I've attached a document called test which gives a clearer picture of the problem I'm experiencing rows containing 18, Test2 and 11013 are right aligned. I just need to align them to the left
[Code]
-- Table
CREATE TABLE [dbo].[Onhand](
[Item] [varchar](18) NULL,
[Company] [varchar](3) NULL,
[Division] [varchar](8) NULL,
[Corporation] [varchar](12) NULL,
[Sold] [varchar](8) NULL,
[Department] [varchar](3) NULL,
[ShipTo] [varchar](12) NULL,
[Quantity] [varchar](8) NULL,
[Weight] [varchar](12) NULL,
[Cube] [varchar](12) NULL,
[MTDSales] [varchar](8) NULL,
[MinimumOrderQuantity] [varchar](8) NULL,
[Multiple] [varchar](8) NULL,
[SafetyTime] [varchar](3) NULL,
[NoBuyFlag] [varchar](1) NULL,
[LeadTime] [varchar](3) NULL,
[Remark] [varchar](16) NULL,
[RemarkIndicator] [varchar](1) NULL,
[CarriageReturn] [varchar](1) NULL,
[LineFeed] [varchar](1) NULL,
[Tonnage] [varchar](22) NULL,
[ValidEntryField] [varchar](1) NULL
)
[/code]
-- Inserting data
INSERT INTO dbo.Onhand(Item,ShipTo,Quantity,Weight,MTDSales)
VALUES('Test3','0','20','1','0.001102')
INSERT INTO dbo.Onhand(Item,ShipTo,Quantity,Weight,MTDSales)
VALUES('18','1','100','1','0.001102')
INSERT INTO dbo.Onhand(Item,ShipTo,Quantity,Weight,MTDSales)
VALUES('11013','1001','2940','1.193','0.001315')
-- My Attempt
SELECT Ltrim(Item) as Item,
LTrim(CASE Company
WHEN 'NULL' THEN ''
ELSE ''
END )AS Company
, CASE Division
WHEN 'NULL' THEN ''
ELSE ''
END AS Division
, CASE Corporation
WHEN 'NULL' THEN ''
ELSE ''
END AS Corporation
, CASE Sold
WHEN 'NULL' THEN ''
ELSE ''
END AS Sold
, CASE Department
WHEN 'NULL' THEN ''
ELSE ''
END AS Department
, ShipTo
, Quantity
, Weight
, CASE Cube
WHEN 'NULL' THEN ''
ELSE ''
END AS Cube
, MTDSales
, CASE [MinimumOrderQuantity]
WHEN 'NULL' THEN ''
ELSE ''
END AS [MinimumOrderQuantity]
, CASE Multiple
WHEN 'NULL' THEN ''
ELSE ''
END AS Multiple
, CASE [SafetyTime]
WHEN 'NULL' THEN ''
ELSE ''
END AS [SafetyTime]
, CASE [NoBuyFlag]
WHEN 'NULL' THEN ''
ELSE ''
END AS [NoBuyFlag]
, CASE [LeadTime]
WHEN 'NULL' THEN ''
ELSE ''
END AS [LeadTime]
, CASE Remark
WHEN 'NULL' THEN ''
ELSE ''
END AS Remark
, CASE [RemarkIndicator]
WHEN 'NULL' THEN ''
ELSE ''
END AS [RemarkIndicator]
, CASE [CarriageReturn]
WHEN 'NULL' THEN ''
ELSE ''
END AS [CarriageReturn]
, CASE [LineFeed]
WHEN 'NULL' THEN ''
ELSE ''
END AS[LineFeed]
FROM [dbo].onhand
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 21, 2011 at 6:12 am
Jnrstevej (6/21/2011)
Thanks for your repliesTo be honest i don't understand the script below, this returns 32 for me.
declare @name varchar(256)
set @name = ' 1231'
select ascii(SUBSTRING(@name , 1 , 1))
I cant get my head round how the above code is meant to work with a replace.
My understanding of the replace is the following how does that relate to the above???
SELECT REPLACE('ReportServices','Report','Analysis_');
I've attached a document called test which gives a clearer picture of the problem I'm experiencing rows containing 18, Test2 and 11013 are right aligned. I just need to align them to the left
--
No document attached.
The ASCII query is returning the ASCII value of the first character in the string. 32 is (from memory) a space character. It was allowing you to check what the characters are and does not do a replace, as you have suggested.
When you talk of right-alignment, what product are you viewing the results in which shows the alignment?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 21, 2011 at 6:44 am
Oh right that clears that up on, the problem is that I'm unsure how to relate that to my script
I'm using a software Notepad ++ and Microsoft standard notepad which both show the right alignment
I've attached the document in my previous post you should see it now
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 21, 2011 at 7:07 am
I created your table, populated the data and then
SELECT * FROM dbo.Onhand o
- there is no right-alignment of anything.
How are you getting the data out of SQL Server and into a file which you view with Notepad? I suspect that it is this process which is causing the problem.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 21, 2011 at 7:07 am
How are you getting the results into notepad ?
Far away is close at hand in the images of elsewhere.
Anon.
June 21, 2011 at 7:08 am
Justification is a presentation issue and T-SQL is not designed to be a presentation layer, so it has no support for presentation, although you can use string manipulation functions to partially control presentation.
To the best of my knowledge Notepad++ and Windows Notepad cannot pull data directly from a SQL Server database. I assume that you are exporting the data to a file and then opening that file in Notepad++ or Windows Notepad. How is that file being created: SSIS? bcp? SSRS?
Many programs will right justify numbers by default, but you can override that by converting the numbers to strings. Some programs will also allow you to specify the justification.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 21, 2011 at 7:26 am
I'm using a custom built integrator(middle ware) that allows one to move data between disparate data sources but also allows you to manage it as well.
So it works by connecting it to an instance in SQL Server 08 via ODBC connection, importing data which could be a txt or CSV file, writing the required scripts such as drop, create, and select statement etc. Then the software allows you to export the data, in my case a fixed length such as a DAT file or txt.
I don't think the problem lies with Notepad++ as previously I've used a ltrim and that seems to have worked. However, this time no such luck,I'm under the impression that if i could get the column(item) to align to the LEFT in the select statement when its exported this should in theory solve my issue.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 21, 2011 at 8:28 am
Jnrstevej (6/21/2011)
I'm under the impression that if i could get the column(item) to align to the LEFT in the select statement when its exported this should in theory solve my issue.
The SELECT statement doesn't have a property for alignment. You can't set a property that doesn't exist. This is a presentation issue and T-SQL is not the presentation layer.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 21, 2011 at 8:36 am
i've done this on the .NET side of things, after i've already loaded the data into a .NET DataTable.
from there, it's fairly easy to format the data into a string from any dataset to be left aligned, right aligned, or however you want it padded / formatted or delimited.
As someone already stated, to do it in the SELECT means casting/converting the data to varchars;
Lowell
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply