Wednesday, March 28, 2012

PAD and Left justify in T SQL

Hi,
Is it possible to PAD and justify output from a select statement? I have to
build an input record from two fields in a table that I am concatenating
together. The result is then going to be passed as a parameter value to a
dll. The dll is looking for input of the form
Field Name position Length
record type 1 1
ClaimID 2 17
some of the ClaimID's are less than 17 characters in length
typical output (an underscore here denotes a space)
Record # Data
1 C_______1234567890
so I need to pad the '1234567890' value with 7 spaces to the left.
At the moment I am doing this after the SELECT from SQL Server but I
wondered if I could do this in the actual SELECT itself?
Any help appreciatedYes, but it is also possible to do this in the DLL or elsewhere in the
presentation area.
(As a demonstration):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1234567890), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1565), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(12345678901234567), 17)
(In your query):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(ClaimID), 17)
So, as long as claimID is never more than 17 characters, this will work. If
you exceed 17, you will trim off the leading character(s).
A
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Joe,
Is this what you're asking for:?
DECLARE @.VAR VARCHAR(50)
SET @.VAR = '1234567890'
SELECT REPLICATE(' ',7) + @.VAR AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
DECLARE @.VAR CHAR(50)
SET @.VAR = '1234567890'
SELECT RTRIM(REPLICATE(' ',7) + @.VAR) AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||This should get you going. Add 17 spaces to right of the ClaimID, and then k
eep the 17 right.most
characters:
SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
FROM
(
SELECT 'C' as c1, '1234567890' AS c2
) AS tbl
I prefer doing this stuff in the client app, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have t
o
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Thank you for the replies , its working well, without help from you guys I
think I would be out of a job.
"Tibor Karaszi" wrote:

> This should get you going. Add 17 spaces to right of the ClaimID, and then
keep the 17 right.most
> characters:
> SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
> FROM
> (
> SELECT 'C' as c1, '1234567890' AS c2
> ) AS tbl
> I prefer doing this stuff in the client app, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
>|||> Thank you for the replies , its working well, without help from you guys I
> think I would be out of a job.
I've often wondered if there is an easy way to divert portions of salaries
of the people we help, then I wouldn't need a job. :-)|||If you're anything like me you couldn't stop this even if you wanted to or
was rich enough to. We'd get bored ;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
> I've often wondered if there is an easy way to divert portions of salaries
> of the people we help, then I wouldn't need a job. :-)
>|||rest assured my salary sucks - perphaps the newsgroup could introduce a
voluntary donation to a charity of choice for really good advice.
"Jerry Spivey" wrote:

> If you're anything like me you couldn't stop this even if you wanted to or
> was rich enough to. We'd get bored ;-)
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
>
>sql

No comments:

Post a Comment