0

I need to link various tables that each have a common key (a serial number in this case). In some tables the key has a leading zero e.g. '037443' and on others it doesn't e.g. '37443'. In both cases the serial refers to the same product. To confound things serial 'numbers' are not always just numeric e.g. may be "BDO1234", in these cases there is never a leading zero.

I'd prefer to use the WHERE statement (WHERE a.key = b.key) but could use joins if required. Is there any way to do this?

I'm still learning so please keep it simple if possible. Many thanks.

Absinthe
  • 3,258
  • 6
  • 31
  • 70
  • [This](http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server) is not an answer to your question, but should lead you in the right direction. – Zohar Peled Jan 26 '16 at 10:17
  • Thanks, that prompted me to update my post because it's not quite that simple, serials are sometime alpha numeric so I need to drop the zero only if it exists if that makes sense? – Absinthe Jan 26 '16 at 10:23

2 Answers2

2

Based on the accepted answer in this link, I've written a small tsql sample to show you what I meant by 'the right direction':

Create the test table:

CREATE TABLE tblTempTest
(
    keyCol varchar(20)
)
GO

Populate it:

INSERT INTO tblTempTest VALUES
('1234'), ('01234'), ('10234'), ('0k234'), ('k2304'), ('00034')

Select values:

SELECT  keyCol, 
        SUBSTRING(keyCol, PATINDEX('%[^0]%', keyCol + '.'), LEN(keyCol)) As trimmed
FROM tblTempTest

Results:

keyCol               trimmed
-------------------- --------------------
1234                 1234
01234                1234
10234                10234
0k234                k234
k2304                k2304
00034                34

Cleanup:

DROP TABLE tblTempTest

Note that the values are alpha-numeric, and only leading zeroes are trimmed.
One possible drawback is that if there is a 0 after a white space it will not be trimmed, but that's an easy fix - just add ltrim:

SUBSTRING(LTRIM(keyCol), PATINDEX('%[^0]%', LTRIM(keyCol + '.')), LEN(keyCol)) As trimmed
Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

You need to create a function

CREATE FUNCTION CompareSerialNumbers(@SerialA varchar(max), @SerialB varchar(max))
RETURNS bit

AS

BEGIN
    DECLARE @ReturnValue AS bit
    IF (ISNUMERIC(@SerialA) = 1 AND ISNUMERIC(@SerialB) = 1)    
    SELECT @ReturnValue = 
        CASE
            WHEN CAST(@SerialA AS int) = CAST(@SerialB AS int) THEN 1
            ELSE 0
        END
    ELSE
        SELECT @ReturnValue = 
        CASE
            WHEN @SerialA = @SerialB THEN 1
            ELSE 0
    END
    RETURN @ReturnValue
END;
GO

If both are numeric then it compares them as integers otherwise it compares them as strings.

Peter Smith
  • 5,528
  • 8
  • 51
  • 77