[dba-SQLServer] SQL Nightmares

Here is your whole function (at least as I understood the requirement). This
was written using the template I mentioned earlier.

<code>
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Arthur Fuller
-- Create date: 2008/03/20
-- Description:    Remove leading zeroes from a string
-- =============================================
CREATE FUNCTION dbo.ZeroLtrim
(
    -- Add the parameters for the function here
    @str Varchar(100)
)
RETURNS Varchar(100)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result Varchar(100)
    WHILE LEFT(@str,1) = '0'
        BEGIN
            SET @str = SUBSTRING(@str,2,LEN(@str)-1)
        END
        -- Add the T-SQL statements to compute the return value here
        SELECT @Result = @str
    -- Return the result of the function
    RETURN @Result

END
GO

SELECT dbo.ZeroLTrim('0000012th Street')
-- returns '12th Street'
</code>

On 3/20/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> Ahh... SELECT...
>
> This is why I find BOL so unhelpful.  The example does not show
>
> SELECT right("Hello",2)
>
> It shows
>
> Right("hello",2)
>
> An example should run, exactly as displayed.
>
> In fact, when I typed in
>
> SELECT right("Hello",2) (which is essentially what is on BOL) I then get
> another error message:
>
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'hello'.
>
> BOL says to use ".  Now when I change the " to a ' the statement works.
>
> I am sure that once I am an expert on the syntax then BOL will be highly
> useful.
>
> I can see that this is going to be a long morning.
>
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
>
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
> Fuller
>
> Sent: Thursday, March 20, 2008 8:49 AM
> To: Discussion concerning MS SQL Server
> Subject: Re: [dba-SQLServer] SQL Nightmares
>
>
> I just successfully ran this:
>
> SELECT RIGHT('abcde', 3)
> and got back 'cde' as expected.
>
> A.
>
> On 3/20/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
> >
> > It doesn't seem to like the Right( part of the function.  Right is not
> > correctly colored.
> >
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>