Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    213
    Provided Answers: 2

    Unanswered: How to match a value with or without leading zeros

    I have a TABLE1 with field Main_ID (char) that contains numbers with or without leading zeros (some have the leading zeros and some don't). I have TABLE2 with Main_ID (char) that does not have the leading zeros. What I am trying to do is create some sort of compare where it will match the Main_ID in TABLE2 with a Main_ID in TABLE1 regardless of if it has leading zeros or not. I am not able to edit the schema or etl processes that pull this data; I have to work with how it is currently set up. Celko need not answer.

    I have thought of 3 different methods to accomplish this in a query, and want to run some tests to see which performs best:
    1. CAST TABLE1.Main_ID to an INT which should strip the leading zeros
    2. Strip the leading zeros by using SUBSTRING and PATINDEX
    3. Create a statement that is true for both leading zeros or no leading zeros (what I have requested above)


    I know how to do 1 and 2. I am looking for help with #3.


    Code:
    CREATE TABLE #test (Main_ID nvarchar(10))
    INSERT INTO #test values ('01'),('02'),('0003')
    I would like to edit the WHERE clause to return '01' from table #test
    Code:
    SELECT Main_ID
    FROM #test
    WHERE Main_ID = '3'
    For reference, here is how I am accomplishing #1 and #2:
    #1
    Code:
    SELECT Main_ID FROM #test WHERE cast(Main_ID AS int) = CAST('3' AS INT)
    #2
    Code:
    SELECT Main_ID FROM #test WHERE SUBSTRING(Main_ID, PATINDEX('%[^0]%', Main_ID), LEN(Main_ID)) = '3'
    Last edited by clawlan; 11-11-16 at 12:26.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    I would go with your first option. String operations in SQL Server tend to be rather expensive. The first option will invalidate indexes, but that is pretty much given in whatever method you go with.

    One way you may be able to get a generic search is to replace 0 with a space, so the regular character comparison will drop leading spaces. The one problem could be that it may drop trailing spaces, so you will have to check with a values like '00100' and '0010' at the same time to make sure they don't collide.
    Code:
    SELECT Main_ID FROM #test WHERE replace(Main_ID, '0', ' ') = replace('3', '0', ' ')

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    213
    Provided Answers: 2
    Quote Originally Posted by MCrowley View Post
    I would go with your first option. String operations in SQL Server tend to be rather expensive. The first option will invalidate indexes, but that is pretty much given in whatever method you go with.

    One way you may be able to get a generic search is to replace 0 with a space, so the regular character comparison will drop leading spaces. The one problem could be that it may drop trailing spaces, so you will have to check with a values like '00100' and '0010' at the same time to make sure they don't collide.
    Code:
    SELECT Main_ID FROM #test WHERE replace(Main_ID, '0', ' ') = replace('3', '0', ' ')
    Oh, replace, that's a great idea. Thanks.

  4. #4
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    strip(main_id,l,'0')

    can create an index on expression as well

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    213
    Provided Answers: 2
    Quote Originally Posted by DNance View Post
    strip(main_id,l,'0')

    can create an index on expression as well
    Strip is not a function in TSQL 2012

  6. #6
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    you are correct and it doesn't support TRIM either, correct? sorry, get used to things on different platforms and forget not all of them are interchangeable.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    Quote Originally Posted by MCrowley View Post
    I would go with your first option. String operations in SQL Server tend to be rather expensive. The first option will invalidate indexes, but that is pretty much given in whatever method you go with.
    ^ this.

    However, I'd add an additional caveat (and therefore slightly different approach)...

    You can't guarantee that your text field will only ever contain "numbers", and therefore casting to a numeric type may fail.
    Yes, later versions of SQL Server include functions such as Try_Cast(), but that doesn't feel like the correct solution.

    Instead; I would suggest that you treat both your source data and search value as strings:
    Code:
    DECLARE @search_value nvarchar(10) = '3';
    
    SELECT Main_ID
         , Right(Replicate('0', 10) + Main_ID, 10) AS search_against
    FROM   #test
    WHERE  Right(Replicate('0', 10) + Main_ID, 10) = Right(Replicate('0', 10) + @search_value, 10)
    ;
    The idea here is that we pad both strings with leading zeroes. Note that the value of "10" repeated here is the length of the source datatype varchar(10).
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    Quote Originally Posted by clawlan View Post
    I am not able to edit the schema or etl processes that pull this data; I have to work with how it is currently set up. Celko need not answer.
    Teeheehee
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •