OK except for a daft problem, which is that the B***ding forum software sees the & # 1 6 0 ; & # 1 6 0 ; ) (without the spaces of course) and interprets the last two characters as a smiley (begorrah!)

[font="Courier New"]CREATE FUNCTION center

  (

    @String VARCHAR(8000),

    @width INT,

    @fillchar VARCHAR(10) = ' '

  )

/*Returns a copy of @String centered in a string of length width, surrounded

by the appropriate number of fillChar characters

e.g.

select dbo.center('Help me please',100,'*')

select dbo.center('error',100,'*!=')

select dbo.center('error',null,null)

select dbo.center(null,null,null)

*/

RETURNS VARCHAR(8000)

AS BEGIN

    IF @string IS NULL

      RETURN NULL

    DECLARE @LenString INT

    DECLARE @LenResult INT

-- Declare the return variable here

    SELECT  @lenString = LEN(@String), @Fillchar = COALESCE(@Fillchar, ' '),

            @width = COALESCE(@Width, LEN(@String) * 2)

    SELECT  @lenResult = CASE WHEN @LenString > @Width THEN @LenString

                              ELSE @width

                         END

    RETURN STUFF(REPLICATE(@fillchar,

                           @lenResult / LEN(REPLACE(@FillChar, ' ', '|'))),

                 ( @LenResult - LEN(@String) + 2 ) / 2, @lenString, @String)

   END

GO[/font]

[font="Courier New"]ALTER FUNCTION dbo.[count]

  (

    @string VARCHAR(8000),

    @Sub VARCHAR(8000),

    @start INT = NULL,

    @end INT = NULL

  )

/* Returns the number of occurrences of substring sub in string s.

Select dbo.count('This is a nice string','[^a-z][a-z]',null,null)--wordcount (not include first word)

Select dbo.count('I''m henery the eighth I am I am','I am',null,null)

select dbo.count('45667892398','8',null,null)

*/

RETURNS INT

AS BEGIN

    DECLARE @more INT

    DECLARE @count INT

    IF @string = NULL

      RETURN NULL

    SELECT  @count = 0, @more = 1, @Start = COALESCE(@Start, 1),

            @end = COALESCE(@end, LEN(@string))

    SELECT  @end = CASE WHEN @end > LEN(@string) THEN LEN(@string)

                        ELSE @end

                   END

    WHILE @more <> 0

      BEGIN

        SELECT  @more = PATINDEX('%' + @sub + '%',

                                 SUBSTRING(@string, @Start, @End - @start + 1))

        IF @more > 0

          SELECT  @Start = @Start + @more, @count = @count + 1

        IF @start >= @End

          SELECT  @more = 0

      END

    RETURN @count

   END

GO[/font]