Drop leading zeros

[fsub]

I am new wih SQL. Does anyone know of a way to drop leading zeros? The field is nvarchar and may contain values such as 00000123. I need to drop the leading zeros so the result would be 123. Some values maybe all zeros in which case might produce a null which is disallowed by the field. Any help would be greatly appreciated.

 

[gmmastros]

Can there be any spaces in your data? If the answer is no, then take a look at this: thread183-356996

If your data contains all 0's, the code suggested in the thread I pointed you to would set the value to an empty string (not null).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

Here's a function I got off Google once. What's in the second parameter is what gets stripped from the front:

-- Create the function
IF OBJECT_ID(N'Lstrip') IS NOT NULL
   DROP FUNCTION Lstrip
GO
CREATE FUNCTION Lstrip
   (
    @String VARCHAR(MAX),
    @chars VARCHAR(255) = ' '
   )
RETURNS VARCHAR(MAX)
AS BEGIN
      SELECT   @Chars = COALESCE(@Chars, ' ')
      IF LEN(@Chars) = 0
         RETURN LTRIM(@String)
      IF @String IS NULL
         RETURN @string
      WHILE PATINDEX('[' + @chars + ']%', @string) = 1
         BEGIN
            SELECT   @String = RIGHT(@string,
                                     LEN(REPLACE(@string, ' ', '|')) - 1)
         END
      RETURN @String
   END
GO

-- Then run
SELECT   dbo.lstrip('0001234', '0')

 

[djj55]

If you only have numerics in the variable then you could use something like

CONVERT(NVARCHAR, CAST(mycolumn, INT))

You can check if the value is numeric by the ISNUMERIC command, thus you could do

UPDATE mytable
SET mycolumn = CASE WHEN ISNUMERIC(mycolumn) = 1 THEN CONVERT(NVARCHAR, CAST(mycolumn, INT))ELSE mycolumn END

This has not been tested and I would suggest looking up any command you are not familiar with.

Good Luck,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!