none
The ltrim function requires 1 argument(s)

    Question

  • Hi All,

    I got below error while using command -- LTRIM (MEG,'0')

    error: The ltrim function requires 1 argument(s).

    i want to remove 7 zeros in front of the number.

    example:

    before: 00000006787

    after: 6787

    Please help :(

    Wednesday, September 25, 2013 2:40 AM

Answers

  • PFB code for work for all type of string -

    Working - Finding first non occurence of 0 and get the substring to the end fo the string.

    select substring(number,PATINDEX('%[^0]%',number),LEN(number))
    from
    (select '00000006787' number union 
    select '00f3456' number union
    select 'ff' union
    select '123') a

    • Proposed as answer by Harsha Lella Thursday, September 26, 2013 5:50 PM
    • Marked as answer by Azyean Friday, September 27, 2013 1:12 PM
    • Unmarked as answer by Azyean Friday, September 27, 2013 1:12 PM
    • Marked as answer by Azyean Friday, September 27, 2013 1:12 PM
    Wednesday, September 25, 2013 3:37 AM

All replies

  • LTRIM function is used to remove leading spaces from a string. In your case, it is not spaces, but leading zeros that you need to remove. And LTRIM() function takes only one argument (that is the string that you want to trim). That is why you are getting the error.

    One simple method that can be applied here is to convert the value to INT (which discards the leading zeros) and then convert back to string. See this example.

    DECLARE @n VARCHAR(100)
    SELECT @n = '00000006787'
    
    SELECT CAST(CAST(@n AS INT) AS VARCHAR) AS Result
    /*
    Result
    ------------------------------
    6787
    */


    My Blog


    Wednesday, September 25, 2013 3:02 AM
    Moderator
  • PFB code for work for all type of string -

    Working - Finding first non occurence of 0 and get the substring to the end fo the string.

    select substring(number,PATINDEX('%[^0]%',number),LEN(number))
    from
    (select '00000006787' number union 
    select '00f3456' number union
    select 'ff' union
    select '123') a

    • Proposed as answer by Harsha Lella Thursday, September 26, 2013 5:50 PM
    • Marked as answer by Azyean Friday, September 27, 2013 1:12 PM
    • Unmarked as answer by Azyean Friday, September 27, 2013 1:12 PM
    • Marked as answer by Azyean Friday, September 27, 2013 1:12 PM
    Wednesday, September 25, 2013 3:37 AM
  • I have used this method and it worked! Thanks :)
    Friday, September 27, 2013 1:14 PM
  • This will not work in T-SQL dialect. You ar trying to write ANSI/ISO Standard SQL. 

     <trim function> ::= TRIM <left paren> <trim operands> <right paren>
     <trim operands> ::= [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source>
     <trim source> ::= <character value expression>
     <trim specification> ::= LEADING | TRAILING | BOTH
     <trim character> ::= <character value expression>

    There are some tricks, but I like adding a dummy character to the left and use replace:

     REPLACE (REPLACE (('#' + '00000006787'), '#0000000', ''), '#', '') 

    You can nest the replace for various lengths of  zero strings. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, September 27, 2013 1:55 PM