|
|
Posted by Mike C# on 10/02/42 11:51
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(' ', @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
>> > > " " 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
>> > > >
>
Navigation:
[Reply to this message]
|