locked
Remove the leading Zero's and reduce length RRS feed

  • Question

  • Hi

     

    I have a table with a column which contains data like:

     

    0000123456_PF_20110531_P_F

     

    Is it possible in a select statement to remove the leading 4 Zero's and the '_PF_20110531_P_F' so i am just left with 123456?

    Some of the data will contain 4 Zero's at the begining some will have 3 like so:

    0001234567_PF_20110531_P_F

     

    Many thanks

    Paul

    Tuesday, May 31, 2011 6:13 PM

Answers

  • im assuming this '_PF_20110531_P_F' is a fixed length part of the field, if so the following expression should work, just replace that ID field with whatever your's is called

    =replace(left(Fields!ID.Value,4),"0","")  &mid(left(Fields!ID.Value,len(Fields!ID.Value)-16),5)

    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:17 PM
    Tuesday, May 31, 2011 8:48 PM
  • Here is how you can 'trim' leading zeroes in T-SQL:

    DECLARE @t TABLE( 
         ColZeroes VARCHAR(30) 
         ) 
    
    INSERT INTO @t 
    SELECT '0000123456_PF_20110531_P_F' 
    UNION ALL 
    SELECT '0001234567_PF_20110531_P_F' 
    UNION ALL 
    SELECT 'This does not have' 
    
    SELECT ColZeroes, 
      CASE 
       WHEN PATINDEX('%[^0]%',ColZeroes) > 0 THEN SUBSTRING(ColZeroes,PATINDEX('%[^0]%',ColZeroes),LEN(ColZeroes)) 
       ELSE ColZeroes 
      END 
    FROM @t

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by PavanKokkula Wednesday, June 1, 2011 4:59 AM
    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:16 PM
    Tuesday, May 31, 2011 11:51 PM
  • Hi,

    Modification in Naomi post to only achieve the number between leading 0's and _PF.

    CASE
       WHEN PATINDEX('%[^0]%',ColZeroes) > 0 AND PATINDEX('%[_]%',ColZeroes) > 0  THEN SUBSTRING(ColZeroes,PATINDEX('%[^0]%',ColZeroes),PATINDEX('%[_PF]%',ColZeroes) - PATINDEX('%[^0]%',ColZeroes))
       ELSE ColZeroes
    END

    Hope its helpful....


    Pavan Kokkula Tata Consultancy Services.
    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:16 PM
    Wednesday, June 1, 2011 4:59 AM
  • HI,

     

         You can do that in the SQL itself select  substring(Column, patindex('%[^0]%',Column), 10)

     

    Regards,

    Bharath R S

    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:16 PM
    Wednesday, June 1, 2011 6:13 AM
  • If you always have - before the rest of the string, then

    select substring(Column, charindex(' - ', Column) + 3, len(Column)) 
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Paul Fallows Friday, June 10, 2011 8:09 AM
    Thursday, June 9, 2011 3:42 AM

All replies

  • im assuming this '_PF_20110531_P_F' is a fixed length part of the field, if so the following expression should work, just replace that ID field with whatever your's is called

    =replace(left(Fields!ID.Value,4),"0","")  &mid(left(Fields!ID.Value,len(Fields!ID.Value)-16),5)

    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:17 PM
    Tuesday, May 31, 2011 8:48 PM
  • Hi Nehemiah Willis

     

    Many thanks for your reply.  However i am getting a few errors when trying to use this.

     

    Incorrect syntax near the keyword 'left'

    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

     

    i assume that i use this as part of the select statement?   My table name is 'documents' and field name is 'name'.

     

    Any ideas?

     

    Many thanks

    Paul

    Tuesday, May 31, 2011 10:44 PM
  • no this would be an expression used in the report in place of where ever you need to use it at.  But this kind of logic can also implemented in the query also.
    Tuesday, May 31, 2011 11:11 PM
  • here is an example doing it in a tsql query:

     

    create table #tmp(col varchar(50));

    insert into #tmp values('0009123456_PF_20110531_P_F');

    insert into #tmp values('0000123456_PF_20110531_P_F');

    select (replace(left(col,4),'0','')  + substring(left(col,len(col)-16),5,30))  from #tmp;

    Tuesday, May 31, 2011 11:25 PM
  • Here is how you can 'trim' leading zeroes in T-SQL:

    DECLARE @t TABLE( 
         ColZeroes VARCHAR(30) 
         ) 
    
    INSERT INTO @t 
    SELECT '0000123456_PF_20110531_P_F' 
    UNION ALL 
    SELECT '0001234567_PF_20110531_P_F' 
    UNION ALL 
    SELECT 'This does not have' 
    
    SELECT ColZeroes, 
      CASE 
       WHEN PATINDEX('%[^0]%',ColZeroes) > 0 THEN SUBSTRING(ColZeroes,PATINDEX('%[^0]%',ColZeroes),LEN(ColZeroes)) 
       ELSE ColZeroes 
      END 
    FROM @t

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by PavanKokkula Wednesday, June 1, 2011 4:59 AM
    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:16 PM
    Tuesday, May 31, 2011 11:51 PM
  • Hi,

    Modification in Naomi post to only achieve the number between leading 0's and _PF.

    CASE
       WHEN PATINDEX('%[^0]%',ColZeroes) > 0 AND PATINDEX('%[_]%',ColZeroes) > 0  THEN SUBSTRING(ColZeroes,PATINDEX('%[^0]%',ColZeroes),PATINDEX('%[_PF]%',ColZeroes) - PATINDEX('%[^0]%',ColZeroes))
       ELSE ColZeroes
    END

    Hope its helpful....


    Pavan Kokkula Tata Consultancy Services.
    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:16 PM
    Wednesday, June 1, 2011 4:59 AM
  • HI,

     

         You can do that in the SQL itself select  substring(Column, patindex('%[^0]%',Column), 10)

     

    Regards,

    Bharath R S

    • Marked as answer by Tony Chain Wednesday, June 8, 2011 1:16 PM
    Wednesday, June 1, 2011 6:13 AM
  • Many thanks everyone for your help.

     

    i have a similar question.  I have data in a table like so:

     

    123456 - test

    598762 - test2

    78952 - test3

    95635 - test4

     

    What i need is the numbers removing so i am left with test, test2 etc. As you can see some of the numbers are 6 long some are 5.

     

    Any ideas?

     

    Many thanks

    Paul

    Wednesday, June 8, 2011 10:28 PM
  • If you always have - before the rest of the string, then

    select substring(Column, charindex(' - ', Column) + 3, len(Column)) 
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Paul Fallows Friday, June 10, 2011 8:09 AM
    Thursday, June 9, 2011 3:42 AM
  • Hi

     

    Many thanks for this - worked a treat.

    I have tried to find a good explanation on using the 'Substring'but have failed.

     

    Would it be possible for you to expain how this works?  Would like to teach myself not just ask for the answers!!

     

    Many thanks for all your help.

    Paul

    Friday, June 10, 2011 8:13 AM
  • SUBSTRING function takes a portion of the string started from the N position (second argument) and ended with the Mth position (specified in the last, 3rd argument).

    So, suppose we have a string 

    'ABC'

    If we specify 2 for the second argument and length of the string as the last argument (it will mean we want to get the whole remainder of the string and we don't know exactly how many characters, so we'll specify a number which will definitely be enough, as if the last argument is greater than the length of the remainder, the substring function takes all remainder):

    declare @str varchar(100)
    set @str = 'ABC'
    select @str, SUBSTRING(@str,2, len('ABC'))
    


    Now, suppose we have a string like 'A - BC' and we know that this string ALWAYS have ' - ' in it. But we don't know ' - ' position.

    So, the first thing is we need to find the position of the ' - ' and since we don't want to include ' - ' in our result, we add 3 (the length of ' - ') to the position found.

    So,

    declare @str varchar(100)
    set @str = 'A - BC'
    select @str, SUBSTRING(@str,CHARINDEX(' - ', @str) + 3, len('ABC'))
    


    There are certain gotchas to look for. Say, the length expression (last argument) must be a positive number.

    See the rest in BOL SUBSTRING()


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, June 10, 2011 11:57 AM