right padding equivalent

[lossed]

Hi everyone,

Please excuse me if this has been asked before or sounds a bit dim.
This is a question asked on another forum but the solutions being
offered are focussing on programming rather than letting the DB server
do the work, which I'm not sure is the most efficient solution.
However, my confession is I dont use SQL server so can't help them
directly with the syntax. Hopefully you can help me help them and learn
a little about SQL Server in the process.

Trying to right pad a first name field so the padded string is a total
of 30 chars. It will be output concatenated with the last name field,
and each field separated with a "|". So that when output it reads
something like:

fname | mylastname


Syntax given was:

select id,
substring((last _name+','+rtrim (' '+isnull(level, '))+'
'+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as student_name
from student

Issue: It appears this is padding correctly but the spaces are not
rendering in the browser. (I have no way to check this as I don't use
sqlserver. However, I can understand that multiple spaces are not going
to render in the client browser, if indeed the query is padding with
spaces.

Question: Instead of using space(), can replicate() be used and a
unicode space representation rather than an actual space be used? Or,
is there a better way that will ensure
the padding shows in browser?
I guess a fixed width font would also need to be used otherwise the
30-char blocks could wind up being different widths, which would defeat
the purpose.

If there is something I've missed, or you have any suggestions, I'm
keen to learn.

TYhanks in advance,

Lossed

[Bucky]

Re: right padding equivalent

lossed wrote:[color=blue]
> Issue: It appears this is padding correctly but the spaces are not
> rendering in the browser. (I have no way to check this as I don't use
> sqlserver. However, I can understand that multiple spaces are not going
> to render in the client browser, if indeed the query is padding with
> spaces.[/color]

You can view source with the browser. Then highlight the text to see
whether the spaces are there or not.

[lossed]

Re: right padding equivalent

Yes but view what?
I do not use sql server so can't build the query to output to the
browser.
Sorry if I did not make that clear enough.


Bucky wrote:[color=blue]
> lossed wrote:[color=green]
> > Issue: It appears this is padding correctly but the spaces are not
> > rendering in the browser. (I have no way to check this as I don't use
> > sqlserver. However, I can understand that multiple spaces are not going
> > to render in the client browser, if indeed the query is padding with
> > spaces.[/color]
>
> You can view source with the browser. Then highlight the text to see
> whether the spaces are there or not.[/color]

[Mike C#]

Re: right padding equivalent

HTML strips extra whitespace out when rendering. Maybe appending a bunch of
" " non-breaking spaces to the end would resolve your display issue?

"lossed" <hundyhunter@gm ail.com> wrote in message
news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .[color=blue]
> Hi everyone,
>
> Please excuse me if this has been asked before or sounds a bit dim.
> This is a question asked on another forum but the solutions being
> offered are focussing on programming rather than letting the DB server
> do the work, which I'm not sure is the most efficient solution.
> However, my confession is I dont use SQL server so can't help them
> directly with the syntax. Hopefully you can help me help them and learn
> a little about SQL Server in the process.
>
> Trying to right pad a first name field so the padded string is a total
> of 30 chars. It will be output concatenated with the last name field,
> and each field separated with a "|". So that when output it reads
> something like:
>
> fname | mylastname
>
>
> Syntax given was:
>
> select id,
> substring((last _name+','+rtrim (' '+isnull(level, '))+'
> '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as student_name
> from student
>
> Issue: It appears this is padding correctly but the spaces are not
> rendering in the browser. (I have no way to check this as I don't use
> sqlserver. However, I can understand that multiple spaces are not going
> to render in the client browser, if indeed the query is padding with
> spaces.
>
> Question: Instead of using space(), can replicate() be used and a
> unicode space representation rather than an actual space be used? Or,
> is there a better way that will ensure
> the padding shows in browser?
> I guess a fixed width font would also need to be used otherwise the
> 30-char blocks could wind up being different widths, which would defeat
> the purpose.
>
> If there is something I've missed, or you have any suggestions, I'm
> keen to learn.
>
> TYhanks in advance,
>
> Lossed
>[/color]

[lossed]

Re: right padding equivalent

Tks Mike,

Do you know what would be the query syntax that will concatenate the
first_name field with the required number of no breaking spaces?

Mike C# wrote:[color=blue]
> HTML strips extra whitespace out when rendering. Maybe appending a bunch of
> "&nbsp;" non-breaking spaces to the end would resolve your display issue?
>
> "lossed" <hundyhunter@gm ail.com> wrote in message
> news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .[color=green]
> > Hi everyone,
> >
> > Please excuse me if this has been asked before or sounds a bit dim.
> > This is a question asked on another forum but the solutions being
> > offered are focussing on programming rather than letting the DB server
> > do the work, which I'm not sure is the most efficient solution.
> > However, my confession is I dont use SQL server so can't help them
> > directly with the syntax. Hopefully you can help me help them and learn
> > a little about SQL Server in the process.
> >
> > Trying to right pad a first name field so the padded string is a total
> > of 30 chars. It will be output concatenated with the last name field,
> > and each field separated with a "|". So that when output it reads
> > something like:
> >
> > fname | mylastname
> >
> >
> > Syntax given was:
> >
> > select id,
> > substring((last _name+','+rtrim (' '+isnull(level, '))+'
> > '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as student_name
> > from student
> >
> > Issue: It appears this is padding correctly but the spaces are not
> > rendering in the browser. (I have no way to check this as I don't use
> > sqlserver. However, I can understand that multiple spaces are not going
> > to render in the client browser, if indeed the query is padding with
> > spaces.
> >
> > Question: Instead of using space(), can replicate() be used and a
> > unicode space representation rather than an actual space be used? Or,
> > is there a better way that will ensure
> > the padding shows in browser?
> > I guess a fixed width font would also need to be used otherwise the
> > 30-char blocks could wind up being different widths, which would defeat
> > the purpose.
> >
> > If there is something I've missed, or you have any suggestions, I'm
> > keen to learn.
> >
> > TYhanks in advance,
> >
> > Lossed
> >[/color][/color]

[SQL Menace]

Re: right padding equivalent

you can also surround it with <pre> tags

save this in a file and open it up in a browser, you will see that the
spaces are preserved
<pre> b b </pre>

Denis the SQL Menace

http://sqlservercode.blogspot.com/

lossed wrote:[color=blue]
> Tks Mike,
>
> Do you know what would be the query syntax that will concatenate the
> first_name field with the required number of no breaking spaces?
>
> Mike C# wrote:[color=green]
> > HTML strips extra whitespace out when rendering. Maybe appending a bunch of
> > "&nbsp;" non-breaking spaces to the end would resolve your display issue?
> >
> > "lossed" <hundyhunter@gm ail.com> wrote in message
> > news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .[color=darkred]
> > > Hi everyone,
> > >
> > > Please excuse me if this has been asked before or sounds a bit dim.
> > > This is a question asked on another forum but the solutions being
> > > offered are focussing on programming rather than letting the DB server
> > > do the work, which I'm not sure is the most efficient solution.
> > > However, my confession is I dont use SQL server so can't help them
> > > directly with the syntax. Hopefully you can help me help them and learn
> > > a little about SQL Server in the process.
> > >
> > > Trying to right pad a first name field so the padded string is a total
> > > of 30 chars. It will be output concatenated with the last name field,
> > > and each field separated with a "|". So that when output it reads
> > > something like:
> > >
> > > fname | mylastname
> > >
> > >
> > > Syntax given was:
> > >
> > > select id,
> > > substring((last _name+','+rtrim (' '+isnull(level, '))+'
> > > '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as student_name
> > > from student
> > >
> > > Issue: It appears this is padding correctly but the spaces are not
> > > rendering in the browser. (I have no way to check this as I don't use
> > > sqlserver. However, I can understand that multiple spaces are not going
> > > to render in the client browser, if indeed the query is padding with
> > > spaces.
> > >
> > > Question: Instead of using space(), can replicate() be used and a
> > > unicode space representation rather than an actual space be used? Or,
> > > is there a better way that will ensure
> > > the padding shows in browser?
> > > I guess a fixed width font would also need to be used otherwise the
> > > 30-char blocks could wind up being different widths, which would defeat
> > > the purpose.
> > >
> > > If there is something I've missed, or you have any suggestions, I'm
> > > keen to learn.
> > >
> > > TYhanks in advance,
> > >
> > > Lossed
> > >[/color][/color][/color]

[lossed]

Re: right padding equivalent

Tks Denis,

Do you know what would be the query syntax that will concatenate the
first_name field with the required number of no breaking spaces?

SQL Menace wrote:[color=blue]
> you can also surround it with <pre> tags
>
> save this in a file and open it up in a browser, you will see that the
> spaces are preserved
> <pre> b b </pre>
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> lossed wrote:[color=green]
> > Tks Mike,
> >
> > Do you know what would be the query syntax that will concatenate the
> > first_name field with the required number of no breaking spaces?
> >
> > Mike C# wrote:[color=darkred]
> > > HTML strips extra whitespace out when rendering. Maybe appending a bunch of
> > > "&nbsp;" non-breaking spaces to the end would resolve your display issue?
> > >
> > > "lossed" <hundyhunter@gm ail.com> wrote in message
> > > news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .
> > > > Hi everyone,
> > > >
> > > > Please excuse me if this has been asked before or sounds a bit dim.
> > > > This is a question asked on another forum but the solutions being
> > > > offered are focussing on programming rather than letting the DB server
> > > > do the work, which I'm not sure is the most efficient solution.
> > > > However, my confession is I dont use SQL server so can't help them
> > > > directly with the syntax. Hopefully you can help me help them and learn
> > > > a little about SQL Server in the process.
> > > >
> > > > Trying to right pad a first name field so the padded string is a total
> > > > of 30 chars. It will be output concatenated with the last name field,
> > > > and each field separated with a "|". So that when output it reads
> > > > something like:
> > > >
> > > > fname | mylastname
> > > >
> > > >
> > > > Syntax given was:
> > > >
> > > > select id,
> > > > substring((last _name+','+rtrim (' '+isnull(level, '))+'
> > > > '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as student_name
> > > > from student
> > > >
> > > > Issue: It appears this is padding correctly but the spaces are not
> > > > rendering in the browser. (I have no way to check this as I don't use
> > > > sqlserver. However, I can understand that multiple spaces are not going
> > > > to render in the client browser, if indeed the query is padding with
> > > > spaces.
> > > >
> > > > Question: Instead of using space(), can replicate() be used and a
> > > > unicode space representation rather than an actual space be used? Or,
> > > > is there a better way that will ensure
> > > > the padding shows in browser?
> > > > I guess a fixed width font would also need to be used otherwise the
> > > > 30-char blocks could wind up being different widths, which would defeat
> > > > the purpose.
> > > >
> > > > If there is something I've missed, or you have any suggestions, I'm
> > > > keen to learn.
> > > >
> > > > TYhanks in advance,
> > > >
> > > > Lossed
> > > >[/color][/color][/color]

[Erland Sommarskog]

Re: right padding equivalent

lossed (hundyhunter@gm ail.com) writes:[color=blue]
> Please excuse me if this has been asked before or sounds a bit dim.
> This is a question asked on another forum but the solutions being
> offered are focussing on programming rather than letting the DB server
> do the work, which I'm not sure is the most efficient solution.
> However, my confession is I dont use SQL server so can't help them
> directly with the syntax. Hopefully you can help me help them and learn
> a little about SQL Server in the process.
>
> Trying to right pad a first name field so the padded string is a total
> of 30 chars. It will be output concatenated with the last name field,
> and each field separated with a "|". So that when output it reads
> something like:
>
> fname | mylastname
>...
> Issue: It appears this is padding correctly but the spaces are not
> rendering in the browser. (I have no way to check this as I don't use
> sqlserver. However, I can understand that multiple spaces are not going
> to render in the client browser, if indeed the query is padding with
> spaces.[/color]

This sounds like an HTML design issue to me. Use a table with an invisible
border, rather than putting &nbsp; and all that stuff in SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[lossed]

Re: right padding equivalent

Tks Erland,

Do you know what would be the query syntax that will concatenate the
first_name field with the required number of no breaking spaces?


Erland Sommarskog wrote:[color=blue]
> lossed (hundyhunter@gm ail.com) writes:[color=green]
> > Please excuse me if this has been asked before or sounds a bit dim.
> > This is a question asked on another forum but the solutions being
> > offered are focussing on programming rather than letting the DB server
> > do the work, which I'm not sure is the most efficient solution.
> > However, my confession is I dont use SQL server so can't help them
> > directly with the syntax. Hopefully you can help me help them and learn
> > a little about SQL Server in the process.
> >
> > Trying to right pad a first name field so the padded string is a total
> > of 30 chars. It will be output concatenated with the last name field,
> > and each field separated with a "|". So that when output it reads
> > something like:
> >
> > fname | mylastname
> >...
> > Issue: It appears this is padding correctly but the spaces are not
> > rendering in the browser. (I have no way to check this as I don't use
> > sqlserver. However, I can understand that multiple spaces are not going
> > to render in the client browser, if indeed the query is padding with
> > spaces.[/color]
>
> This sounds like an HTML design issue to me. Use a table with an invisible
> border, rather than putting &nbsp; and all that stuff in SQL Server.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

[Mike C#]

Re: right padding equivalent

You could create a UDF like this to do it:

CREATE FUNCTION dbo.fn_HtmlHard Pad(@string VARCHAR(200), @length INT)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN RTRIM(@string) + REPLICATE('&nbs p;', @length - LEN(@string))
END
GO

DECLARE @test CHAR(30)
DECLARE @padded VARCHAR(300)
SELECT @test = 'Hello there'
SELECT @padded = dbo.fn_HtmlHard Pad(@test, 20)
SELECT @padded

"lossed" <lossedspam@hot mail.com> wrote in message
news:1151528687 .660240.231810@ d56g2000cwd.goo glegroups.com.. .[color=blue]
> Tks Denis,
>
> Do you know what would be the query syntax that will concatenate the
> first_name field with the required number of no breaking spaces?
>
> SQL Menace wrote:[color=green]
>> you can also surround it with <pre> tags
>>
>> save this in a file and open it up in a browser, you will see that the
>> spaces are preserved
>> <pre> b b </pre>
>>
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>
>> lossed wrote:[color=darkred]
>> > Tks Mike,
>> >
>> > Do you know what would be the query syntax that will concatenate the
>> > first_name field with the required number of no breaking spaces?
>> >
>> > Mike C# wrote:
>> > > HTML strips extra whitespace out when rendering. Maybe appending a
>> > > bunch of
>> > > "&nbsp;" non-breaking spaces to the end would resolve your display
>> > > issue?
>> > >
>> > > "lossed" <hundyhunter@gm ail.com> wrote in message
>> > > news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .
>> > > > Hi everyone,
>> > > >
>> > > > Please excuse me if this has been asked before or sounds a bit dim.
>> > > > This is a question asked on another forum but the solutions being
>> > > > offered are focussing on programming rather than letting the DB
>> > > > server
>> > > > do the work, which I'm not sure is the most efficient solution.
>> > > > However, my confession is I dont use SQL server so can't help them
>> > > > directly with the syntax. Hopefully you can help me help them and
>> > > > learn
>> > > > a little about SQL Server in the process.
>> > > >
>> > > > Trying to right pad a first name field so the padded string is a
>> > > > total
>> > > > of 30 chars. It will be output concatenated with the last name
>> > > > field,
>> > > > and each field separated with a "|". So that when output it reads
>> > > > something like:
>> > > >
>> > > > fname | mylastname
>> > > >
>> > > >
>> > > > Syntax given was:
>> > > >
>> > > > select id,
>> > > > substring((last _name+','+rtrim (' '+isnull(level, '))+'
>> > > > '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as
>> > > > student_name
>> > > > from student
>> > > >
>> > > > Issue: It appears this is padding correctly but the spaces are not
>> > > > rendering in the browser. (I have no way to check this as I don't
>> > > > use
>> > > > sqlserver. However, I can understand that multiple spaces are not
>> > > > going
>> > > > to render in the client browser, if indeed the query is padding
>> > > > with
>> > > > spaces.
>> > > >
>> > > > Question: Instead of using space(), can replicate() be used and a
>> > > > unicode space representation rather than an actual space be used?
>> > > > Or,
>> > > > is there a better way that will ensure
>> > > > the padding shows in browser?
>> > > > I guess a fixed width font would also need to be used otherwise the
>> > > > 30-char blocks could wind up being different widths, which would
>> > > > defeat
>> > > > the purpose.
>> > > >
>> > > > If there is something I've missed, or you have any suggestions, I'm
>> > > > keen to learn.
>> > > >
>> > > > TYhanks in advance,
>> > > >
>> > > > Lossed
>> > > >[/color][/color]
>[/color]

[lossed]

Re: right padding equivalent

Thanks very much Mike,

That's getting more along the lines I had hoped for.
Is there a way to simplify it into just the one select statement rather
than a udf?
I'm thinking along these lines:

SELECT RTRIM(last_name ) + REPLICATE('&nbs p;', 30 - LEN(last_name)) + '
| ' + first_name AS student_name
FROM student

Would that work?

Thanks for your time and help thus far.

Mike C# wrote:[color=blue]
> You could create a UDF like this to do it:
>
> CREATE FUNCTION dbo.fn_HtmlHard Pad(@string VARCHAR(200), @length INT)
> RETURNS VARCHAR(500)
> AS
> BEGIN
> RETURN RTRIM(@string) + REPLICATE('&nbs p;', @length - LEN(@string))
> END
> GO
>
> DECLARE @test CHAR(30)
> DECLARE @padded VARCHAR(300)
> SELECT @test = 'Hello there'
> SELECT @padded = dbo.fn_HtmlHard Pad(@test, 20)
> SELECT @padded
>
> "lossed" <lossedspam@hot mail.com> wrote in message
> news:1151528687 .660240.231810@ d56g2000cwd.goo glegroups.com.. .[color=green]
> > Tks Denis,
> >
> > Do you know what would be the query syntax that will concatenate the
> > first_name field with the required number of no breaking spaces?
> >
> > SQL Menace wrote:[color=darkred]
> >> you can also surround it with <pre> tags
> >>
> >> save this in a file and open it up in a browser, you will see that the
> >> spaces are preserved
> >> <pre> b b </pre>
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >> lossed wrote:
> >> > Tks Mike,
> >> >
> >> > Do you know what would be the query syntax that will concatenate the
> >> > first_name field with the required number of no breaking spaces?
> >> >
> >> > Mike C# wrote:
> >> > > HTML strips extra whitespace out when rendering. Maybe appending a
> >> > > bunch of
> >> > > "&nbsp;" non-breaking spaces to the end would resolve your display
> >> > > issue?
> >> > >
> >> > > "lossed" <hundyhunter@gm ail.com> wrote in message
> >> > > news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .
> >> > > > Hi everyone,
> >> > > >
> >> > > > Please excuse me if this has been asked before or sounds a bit dim.
> >> > > > This is a question asked on another forum but the solutions being
> >> > > > offered are focussing on programming rather than letting the DB
> >> > > > server
> >> > > > do the work, which I'm not sure is the most efficient solution.
> >> > > > However, my confession is I dont use SQL server so can't help them
> >> > > > directly with the syntax. Hopefully you can help me help them and
> >> > > > learn
> >> > > > a little about SQL Server in the process.
> >> > > >
> >> > > > Trying to right pad a first name field so the padded string is a
> >> > > > total
> >> > > > of 30 chars. It will be output concatenated with the last name
> >> > > > field,
> >> > > > and each field separated with a "|". So that when output it reads
> >> > > > something like:
> >> > > >
> >> > > > fname | mylastname
> >> > > >
> >> > > >
> >> > > > Syntax given was:
> >> > > >
> >> > > > select id,
> >> > > > substring((last _name+','+rtrim (' '+isnull(level, '))+'
> >> > > > '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as
> >> > > > student_name
> >> > > > from student
> >> > > >
> >> > > > Issue: It appears this is padding correctly but the spaces are not
> >> > > > rendering in the browser. (I have no way to check this as I don't
> >> > > > use
> >> > > > sqlserver. However, I can understand that multiple spaces are not
> >> > > > going
> >> > > > to render in the client browser, if indeed the query is padding
> >> > > > with
> >> > > > spaces.
> >> > > >
> >> > > > Question: Instead of using space(), can replicate() be used and a
> >> > > > unicode space representation rather than an actual space be used?
> >> > > > Or,
> >> > > > is there a better way that will ensure
> >> > > > the padding shows in browser?
> >> > > > I guess a fixed width font would also need to be used otherwise the
> >> > > > 30-char blocks could wind up being different widths, which would
> >> > > > defeat
> >> > > > the purpose.
> >> > > >
> >> > > > If there is something I've missed, or you have any suggestions, I'm
> >> > > > keen to learn.
> >> > > >
> >> > > > TYhanks in advance,
> >> > > >
> >> > > > Lossed
> >> > > >[/color]
> >[/color][/color]

[Mike C#]

Re: right padding equivalent

That should do the trick. Just make sure that LEN(last_name) can't be
greater than 30 characters or you might get an error message from the
replicate() function.

"lossed" <lossedspam@hot mail.com> wrote in message
news:1151552706 .557852.103250@ b68g2000cwa.goo glegroups.com.. .[color=blue]
> Thanks very much Mike,
>
> That's getting more along the lines I had hoped for.
> Is there a way to simplify it into just the one select statement rather
> than a udf?
> I'm thinking along these lines:
>
> SELECT RTRIM(last_name ) + REPLICATE('&nbs p;', 30 - LEN(last_name)) + '
> | ' + first_name AS student_name
> FROM student
>
> Would that work?
>
> Thanks for your time and help thus far.
>
> Mike C# wrote:[color=green]
>> You could create a UDF like this to do it:
>>
>> CREATE FUNCTION dbo.fn_HtmlHard Pad(@string VARCHAR(200), @length INT)
>> RETURNS VARCHAR(500)
>> AS
>> BEGIN
>> RETURN RTRIM(@string) + REPLICATE('&nbs p;', @length - LEN(@string))
>> END
>> GO
>>
>> DECLARE @test CHAR(30)
>> DECLARE @padded VARCHAR(300)
>> SELECT @test = 'Hello there'
>> SELECT @padded = dbo.fn_HtmlHard Pad(@test, 20)
>> SELECT @padded
>>
>> "lossed" <lossedspam@hot mail.com> wrote in message
>> news:1151528687 .660240.231810@ d56g2000cwd.goo glegroups.com.. .[color=darkred]
>> > Tks Denis,
>> >
>> > Do you know what would be the query syntax that will concatenate the
>> > first_name field with the required number of no breaking spaces?
>> >
>> > SQL Menace wrote:
>> >> you can also surround it with <pre> tags
>> >>
>> >> save this in a file and open it up in a browser, you will see that the
>> >> spaces are preserved
>> >> <pre> b b </pre>
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >> lossed wrote:
>> >> > Tks Mike,
>> >> >
>> >> > Do you know what would be the query syntax that will concatenate the
>> >> > first_name field with the required number of no breaking spaces?
>> >> >
>> >> > Mike C# wrote:
>> >> > > HTML strips extra whitespace out when rendering. Maybe appending
>> >> > > a
>> >> > > bunch of
>> >> > > "&nbsp;" non-breaking spaces to the end would resolve your display
>> >> > > issue?
>> >> > >
>> >> > > "lossed" <hundyhunter@gm ail.com> wrote in message
>> >> > > news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .
>> >> > > > Hi everyone,
>> >> > > >
>> >> > > > Please excuse me if this has been asked before or sounds a bit
>> >> > > > dim.
>> >> > > > This is a question asked on another forum but the solutions
>> >> > > > being
>> >> > > > offered are focussing on programming rather than letting the DB
>> >> > > > server
>> >> > > > do the work, which I'm not sure is the most efficient solution.
>> >> > > > However, my confession is I dont use SQL server so can't help
>> >> > > > them
>> >> > > > directly with the syntax. Hopefully you can help me help them
>> >> > > > and
>> >> > > > learn
>> >> > > > a little about SQL Server in the process.
>> >> > > >
>> >> > > > Trying to right pad a first name field so the padded string is a
>> >> > > > total
>> >> > > > of 30 chars. It will be output concatenated with the last name
>> >> > > > field,
>> >> > > > and each field separated with a "|". So that when output it
>> >> > > > reads
>> >> > > > something like:
>> >> > > >
>> >> > > > fname | mylastname
>> >> > > >
>> >> > > >
>> >> > > > Syntax given was:
>> >> > > >
>> >> > > > select id,
>> >> > > > substring((last _name+','+rtrim (' '+isnull(level, '))+'
>> >> > > > '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as
>> >> > > > student_name
>> >> > > > from student
>> >> > > >
>> >> > > > Issue: It appears this is padding correctly but the spaces are
>> >> > > > not
>> >> > > > rendering in the browser. (I have no way to check this as I
>> >> > > > don't
>> >> > > > use
>> >> > > > sqlserver. However, I can understand that multiple spaces are
>> >> > > > not
>> >> > > > going
>> >> > > > to render in the client browser, if indeed the query is padding
>> >> > > > with
>> >> > > > spaces.
>> >> > > >
>> >> > > > Question: Instead of using space(), can replicate() be used and
>> >> > > > a
>> >> > > > unicode space representation rather than an actual space be
>> >> > > > used?
>> >> > > > Or,
>> >> > > > is there a better way that will ensure
>> >> > > > the padding shows in browser?
>> >> > > > I guess a fixed width font would also need to be used otherwise
>> >> > > > the
>> >> > > > 30-char blocks could wind up being different widths, which would
>> >> > > > defeat
>> >> > > > the purpose.
>> >> > > >
>> >> > > > If there is something I've missed, or you have any suggestions,
>> >> > > > I'm
>> >> > > > keen to learn.
>> >> > > >
>> >> > > > TYhanks in advance,
>> >> > > >
>> >> > > > Lossed
>> >> > > >
>> >[/color][/color]
>[/color]

[lossed]

Re: right padding equivalent

Thank you so much Mike.
Very much appreciated.

Mike C# wrote:[color=blue]
> That should do the trick. Just make sure that LEN(last_name) can't be
> greater than 30 characters or you might get an error message from the
> replicate() function.
>
> "lossed" <lossedspam@hot mail.com> wrote in message
> news:1151552706 .557852.103250@ b68g2000cwa.goo glegroups.com.. .[color=green]
> > Thanks very much Mike,
> >
> > That's getting more along the lines I had hoped for.
> > Is there a way to simplify it into just the one select statement rather
> > than a udf?
> > I'm thinking along these lines:
> >
> > SELECT RTRIM(last_name ) + REPLICATE('&nbs p;', 30 - LEN(last_name)) + '
> > | ' + first_name AS student_name
> > FROM student
> >
> > Would that work?
> >
> > Thanks for your time and help thus far.
> >
> > Mike C# wrote:[color=darkred]
> >> You could create a UDF like this to do it:
> >>
> >> CREATE FUNCTION dbo.fn_HtmlHard Pad(@string VARCHAR(200), @length INT)
> >> RETURNS VARCHAR(500)
> >> AS
> >> BEGIN
> >> RETURN RTRIM(@string) + REPLICATE('&nbs p;', @length - LEN(@string))
> >> END
> >> GO
> >>
> >> DECLARE @test CHAR(30)
> >> DECLARE @padded VARCHAR(300)
> >> SELECT @test = 'Hello there'
> >> SELECT @padded = dbo.fn_HtmlHard Pad(@test, 20)
> >> SELECT @padded
> >>
> >> "lossed" <lossedspam@hot mail.com> wrote in message
> >> news:1151528687 .660240.231810@ d56g2000cwd.goo glegroups.com.. .
> >> > Tks Denis,
> >> >
> >> > Do you know what would be the query syntax that will concatenate the
> >> > first_name field with the required number of no breaking spaces?
> >> >
> >> > SQL Menace wrote:
> >> >> you can also surround it with <pre> tags
> >> >>
> >> >> save this in a file and open it up in a browser, you will see that the
> >> >> spaces are preserved
> >> >> <pre> b b </pre>
> >> >>
> >> >> Denis the SQL Menace
> >> >> http://sqlservercode.blogspot.com/
> >> >>
> >> >> lossed wrote:
> >> >> > Tks Mike,
> >> >> >
> >> >> > Do you know what would be the query syntax that will concatenate the
> >> >> > first_name field with the required number of no breaking spaces?
> >> >> >
> >> >> > Mike C# wrote:
> >> >> > > HTML strips extra whitespace out when rendering. Maybe appending
> >> >> > > a
> >> >> > > bunch of
> >> >> > > "&nbsp;" non-breaking spaces to the end would resolve your display
> >> >> > > issue?
> >> >> > >
> >> >> > > "lossed" <hundyhunter@gm ail.com> wrote in message
> >> >> > > news:1151445410 .988182.165360@ x69g2000cwx.goo glegroups.com.. .
> >> >> > > > Hi everyone,
> >> >> > > >
> >> >> > > > Please excuse me if this has been asked before or sounds a bit
> >> >> > > > dim.
> >> >> > > > This is a question asked on another forum but the solutions
> >> >> > > > being
> >> >> > > > offered are focussing on programming rather than letting the DB
> >> >> > > > server
> >> >> > > > do the work, which I'm not sure is the most efficient solution.
> >> >> > > > However, my confession is I dont use SQL server so can't help
> >> >> > > > them
> >> >> > > > directly with the syntax. Hopefully you can help me help them
> >> >> > > > and
> >> >> > > > learn
> >> >> > > > a little about SQL Server in the process.
> >> >> > > >
> >> >> > > > Trying to right pad a first name field so the padded string is a
> >> >> > > > total
> >> >> > > > of 30 chars. It will be output concatenated with the last name
> >> >> > > > field,
> >> >> > > > and each field separated with a "|". So that when output it
> >> >> > > > reads
> >> >> > > > something like:
> >> >> > > >
> >> >> > > > fname | mylastname
> >> >> > > >
> >> >> > > >
> >> >> > > > Syntax given was:
> >> >> > > >
> >> >> > > > select id,
> >> >> > > > substring((last _name+','+rtrim (' '+isnull(level, '))+'
> >> >> > > > '+rtrim(isnull( first_name,'))+ space(30)),1,30 )+ ' | ' as
> >> >> > > > student_name
> >> >> > > > from student
> >> >> > > >
> >> >> > > > Issue: It appears this is padding correctly but the spaces are
> >> >> > > > not
> >> >> > > > rendering in the browser. (I have no way to check this as I
> >> >> > > > don't
> >> >> > > > use
> >> >> > > > sqlserver. However, I can understand that multiple spaces are
> >> >> > > > not
> >> >> > > > going
> >> >> > > > to render in the client browser, if indeed the query is padding
> >> >> > > > with
> >> >> > > > spaces.
> >> >> > > >
> >> >> > > > Question: Instead of using space(), can replicate() be used and
> >> >> > > > a
> >> >> > > > unicode space representation rather than an actual space be
> >> >> > > > used?
> >> >> > > > Or,
> >> >> > > > is there a better way that will ensure
> >> >> > > > the padding shows in browser?
> >> >> > > > I guess a fixed width font would also need to be used otherwise
> >> >> > > > the
> >> >> > > > 30-char blocks could wind up being different widths, which would
> >> >> > > > defeat
> >> >> > > > the purpose.
> >> >> > > >
> >> >> > > > If there is something I've missed, or you have any suggestions,
> >> >> > > > I'm
> >> >> > > > keen to learn.
> >> >> > > >
> >> >> > > > TYhanks in advance,
> >> >> > > >
> >> >> > > > Lossed
> >> >> > > >
> >> >[/color]
> >[/color][/color]

[Erland Sommarskog]

Re: right padding equivalent

lossed (lossedspam@hot mail.com) writes:[color=blue]
> Do you know what would be the query syntax that will concatenate the
> first_name field with the required number of no breaking spaces?[/color]

Apparently you did not read my post, but only reply to whatever come
in the way.

Not that I would ever get the idea to picture myself as a expert on
HTML authoring, but using &nbsp; to fill up text does not sound like
a good idea. Least of all with a proportional font...

So I maintain that a <TABLE> tag is the way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx