Joining using a series field

Question

Is there a way to join tables on one field that represents a series of valid values? For example, one table has a number or string with "51234" and the other has something like "46610..46680|48670|50000..54280|48240". If there isn't a way to directly join these, is there a way to convert that series to rows with each number in it for a table variable or something like that?

That series column is being generated from Dynamics Nav 5.0 SP1 for general ledger accounts being mapped to departments. I have very little experience with that system, and a LOT of what we have was customizations that a third party was contracted to do, so that may or may not even be standard... I don't even have direct access to the database for that either, but as far as I can tell, that series is being stored as a varchar(100).

I've searched around a lot, but I haven't seen anything like what I need to do. Hopefully someone here has some ideas..

No, this is **not** standard. These values should have been stored in a many-to-many tables. — Oded, Mar 28 '12 at 16:06

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

Firstly, that's poor database design so shame on Dynamics Nav or your 3rd party contractor they should know better. You can join tables on columns that are stored like that.

It may be possible to construct a LIKE clause that works for you t1 JOIN t2 ON t1.id LIKE ('%' + t2.id + '%')

So if t1 Were '46610|46680|50000|' and t2 Were 46680 this would be a match. However, this will quickly get unruly and may be difficult to accomplish exactly what you are trying to do.

Your best bet is to normalize the data. Which is doing exactly what you suggested and breaking out each of the values into their own row. While there is no internal 'split' function that will accomplish this. Here is an SO post about split for sql server : link.

Honestly, though this sounds like a problem that may be beyond your ability to handle (no direct access to DB). I would suggest hiring someone to re-write this to create normalized data in the first place or requesting whomever does have access to fix this process.

edited May 23 '17 at 10:34

Community

1
1

answered Mar 28 '12 at 16:23

Ben English

3,900
2
22
32

Thanks, I can usually get some basic access to query individual tables, but we keep that DB locked down a lot, so I don't just have full access, as I do with most other DBs we have. It looked like that link just handles splits, although I think I may have done something with ranges of numbers like that before in school.. – Korn1699 Mar 28 '12 at 16:32
When you say ranges, do you mean that you have data like `3000|1000-1050` which would mean account 3000 and accounts 1000 through 1050? If so then your data is even more messed up than I realized and this will require a fair amount of custom code to resolve. If that's not the case then you can use split to split apart the '1000|1001|1002' into separate fields/rows. Split means to take '1000|1001|1002' and create an array/set of individual values {1000,1001,1002} which you could process individually. – Ben English Mar 28 '12 at 18:46
regarding ranges: You will still have to split the data into individual fields to be able to evaluate them. Then you can use BETWEEN to select values like `SELECT * FROM t1 WHERE t1.account_id BETWEEN 1000 AND 3000` – Ben English Mar 28 '12 at 18:50
yeah, I meant ranges like that. I know I've seen them written somewhere before with ".." instead of "-", as they are in the DB. I just have no idea where. I think what I am going to do is split on "|" then split on ".." if there is one. If there is a "..", the left part will be set as a begin column and the right will be an end column. If there isn't one, that will be the beginning and end. Each department has a different set of ranges of accounts, and most numbers in the rows aren't actual accounts, so have a begin and end would require a lot less rows. – Korn1699 Mar 29 '12 at 00:39

score 1 · Answer 2 · answered Mar 29 '12 at 16:05

I ended up getting it to do what I needed to with this:

create PROCEDURE [dbo].[spv_parseGLAccounts] (
    @AllowableAccountFilter AS varchar(250),
    @DeptID AS varchar(6),
    @CompCode AS varchar(6)) AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @FirstOrLocation int, @FirstRangeLocation int;
    DECLARE @LeftPart varchar(250), @RightPart varchar(250)
    SET @AllowableAccountFilter = LTRIM(RTRIM(@AllowableAccountFilter))
    SET @FirstOrLocation = CHARINDEX('|',@AllowableAccountFilter)
    SET @FirstRangeLocation = CHARINDEX('.',@AllowableAccountFilter)

    IF @FirstOrLocation <> 0    -- Split on |
        BEGIN
            SET @LeftPart = SUBSTRING(@AllowableAccountFilter, 1, @FirstOrLocation - 1)
            SET @RightPart = SUBSTRING(@AllowableAccountFilter, @FirstOrLocation + 1, LEN(@AllowableAccountFilter) - @FirstOrLocation)
            EXEC spv_parseGLAccounts @LeftPart, @DeptID, @CompCode
            EXEC spv_parseGLAccounts @RightPart, @DeptID, @CompCode
        END
    ELSE IF @FirstRangeLocation <> 0 -- Split on ..
        BEGIN
            INSERT INTO ValidAccountMapping (DeptID, CompCode, BeginAccount, EndAccount)
                VALUES (@DeptID, @CompCode, SUBSTRING(@AllowableAccountFilter, 1, @FirstRangeLocation - 1), 
                    SUBSTRING(@AllowableAccountFIlter, @FirstRangeLocation + 2, LEN(@AllowableAccountFilter) - @FirstRangeLocation - 1))
        END
    ELSE
        BEGIN
            INSERT INTO ValidAccountMapping (DeptID, CompCode, BeginAccount, EndAccount)
                VALUES (@DeptID, @CompCode, @AllowableAccountFilter, @AllowableAccountFilter)
        END 
END

Joining using a series field

2 Answers2