18

T-SQL Join on the foreign key that has zero

 4 years ago
source link: https://www.codesd.com/item/t-sql-join-on-the-foreign-key-that-has-zero.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

T-SQL Join on the foreign key that has zero

advertisements

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.

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

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK