Posted by lossed on 10/02/74 11:51

Thank you so much Mike.
Very much appreciated.

Mike C# wrote:
> 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@hotmail.com> wrote in message
> news:1151552706.557852.103250@b68g2000cwa.googlegroups.com...
> > 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('&nbsp;', 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:
> >> You could create a UDF like this to do it:
> >>
> >> CREATE FUNCTION dbo.fn_HtmlHardPad(@string VARCHAR(200), @length INT)
> >> RETURNS VARCHAR(500)
> >> AS
> >> BEGIN
> >> RETURN RTRIM(@string) + REPLICATE('&nbsp;', @length - LEN(@string))
> >> END
> >> GO
> >>
> >> DECLARE @test CHAR(30)
> >> DECLARE @padded VARCHAR(300)
> >> SELECT @test = 'Hello there'
> >> SELECT @padded = dbo.fn_HtmlHardPad(@test, 20)
> >> SELECT @padded
> >>
> >> "lossed" <lossedspam@hotmail.com> wrote in message
> >> news:1151528687.660240.231810@d56g2000cwd.googlegroups.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@gmail.com> wrote in message
> >> >> > > news:1151445410.988182.165360@x69g2000cwx.googlegroups.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
> >> >> > > >
> >> >
> >

[Back to original message]