Script to get all numbers between Characters

Question

I need to find a way to get the numbers between the dashes. This is the only way I know to do it, but I know that not all of our accounts are the same length. So I am just looking for a way to get everything between before, after and between the dashes. This is an example of the types of accounts we have. '2-0-200-325-0' and '1-0-1105-1500-1520' The non-digit characters are only dashes and nothing else.

 declare @Department Int
 declare @Account Int
 declare @Company Int
 declare @Location Int
 declare @SubAccount Int
 declare @AccountNo varchar(24) = '2-0-200-325-0'
 declare @CommaPos Int

 select @CommaPos = charindex('-',@accountno)

 set @Company = substring(@accountno,1,@CommaPos-1)
 select @Company as Company

 set @Location = Substring(@AccountNo, @CommaPos+1, 1)
 select @Location as Location

 set @Department = Substring(@AccountNo, @CommaPos+3, 4)
 select @Department as Department

 set @Account = Substring(@AccountNo, @CommaPos+8, 4)
 select @Account as Account

 set @SubAccount = Substring(@AccountNo, @CommaPos+13, 4)
 select @SubAccount as SubAccount

Answer 1

/*
    --  First Create this function. This is what you need.
    --  It will split a sentence into words, given a defined separator

        CREATE FUNCTION [dbo].[udf_SplitString] (@Sentence varchar(max), @Separator char(1))
                RETURNS @WordList TABLE (Word varchar(50)) 
            AS
                BEGIN
                    SET @Separator  =   ISNULL(@Separator, ' ') 
                    DECLARE @Word   varchar(50)
                    SET @Sentence = LTRIM(@Sentence) + @Separator   
                    WHILE   (CHARINDEX(@Separator, @Sentence) > 0)
                        BEGIN
                            SET @Word = SUBSTRING(@Sentence, 1, CHARINDEX(@Separator, @Sentence) - 1)
                            INSERT INTO @WordList   SELECT LTRIM(@Word)
                            -- Remove word added to the List from the sentence.
                            SET @Sentence =  SUBSTRING(@Sentence,   CHARINDEX(@Separator, @Sentence) + 1,   LEN(@Sentence))
                            SET @Sentence =  LTRIM(@Sentence)           
                    END                 
                    RETURN
                END 
        GO
*/

DECLARE     @AccountList    TABLE   (AccountNo varchar(20), Variable varchar(20))
INSERT INTO @AccountList    VALUES
            ('1-0-1105-1200-1290','')
        ,   ('1-0-1105-1500-1520','')
        ,   ('1-0-1105-1500-1620','')
        ,   ('1-0-1106-1200-1250','')
        ,   ('1-0-1106-1200-1290','')
        ,   ('1-0-1106-1500-1520','')
;

DECLARE     @VariableList       TABLE   (OrderNo int, VariableName varchar(20))
INSERT INTO @VariableList       VALUES
            (1, 'Company    ')
        ,   (2, 'Location   ')
        ,   (3, 'Department ')
        ,   (4, 'Account    ')
        ,   (5, 'SubAccount ')
;

SELECT
        AccountNo
    ,   Variable    =   (SELECT VariableName FROM @VariableList WHERE RowNo = OrderNo)
    ,   Value       =   Word
FROM    (   
            SELECT  
                    RowNo       =   ROW_NUMBER() OVER(PARTITION BY AccountNo ORDER BY AccountNo)
                ,   AccountNo   =   L.AccountNo
                ,   Variable    =   ''
                ,   Word        =   W.Word

            FROM        @AccountList                            L
            CROSS APPLY dbo.udf_SplitString(L.AccountNo, '-')   W   -- Here how to use the function

        )   R

Answer 2

This was my approach:

--first I use a declared table variable to simulate your issue:

DECLARE @tbl TABLE(ID INT IDENTITY,ACCOUNT_NO VARCHAR(24))
INSERT INTO @tbl VALUES('2-0-200-325-0');

--The query

SELECT t.ID
      ,t.ACCOUNT_NO
      ,casted.value('x[1]','int') AS Company
      ,casted.value('x[2]','int') AS Location
      ,casted.value('x[3]','int') AS Department
      ,casted.value('x[4]','int') AS Account
      ,casted.value('x[5]','int') AS SubAccount
FROM @tbl t
CROSS APPLY(VALUES(CAST('<x>' + REPLACE(t.ACCOUNT_NO,'-','</x><x>') + '</x>' AS XML))) A(casted);

The idea in short:

• We use simple string operations to transform your dash-separated list of numbers into XML.
• Now we can use XQuery to retrieve each element by its position (typesafe!).

Find details here. In this link there is also a faster approach using JSON support (needs v2016+):

SELECT t.ID
      ,t.ACCOUNT_NO
      ,A.*
FROM @tbl t
CROSS APPLY OPENJSON(CONCAT('[[',REPLACE(t.ACCOUNT_NO,'-',','),']]'))
            WITH(Company    INT '$[0]'
                ,Location   INT '$[1]'
                ,Department INT '$[2]'
                ,Account    INT '$[3]'
                ,SubAccount INT '$[4]') A;

The idea of this JSON approach:

• Agains we use some simple string operations to transform your string into a JSON array.
• Using two array brackets ([[) allows to use OPENJSON() with a WITH clause.
• The WITH clause allows to grab each fragment by its (zero-based) position (typesafe).
• The WITH clause is some kind of implicit pivoting.

Answer 3

One option uses a recursive query for parsing. This properly handles the variable lenght of each part - and can easily be extended to handle more parts if needed.

-- declare the variables
declare @AccountNo varchar(24) = '2-0-200-325-0';
declare @Department Int;
declare @Account Int;
declare @Company Int;
declare @Location Int;
declare @SubAccount Int;

-- parse and assign values to variables
with cte as (
    select 
        substring(@AccountNo + '-', 1, charindex('-', @AccountNo + '-') - 1) val,
        substring(@AccountNo + '-', charindex('-', @AccountNo + '-') + 1, len(@AccountNo)) rest,
        1 lvl
    union all
    select
        substring(rest, 1, charindex('-', rest) - 1),
        substring(rest, charindex('-', rest) + 1, len(rest)),
        lvl + 1
    from cte
    where charindex('-', rest) > 0
)
select 
    @Company = max(case when lvl = 1 then val end),
    @Location = max(case when lvl = 2 then val end),
    @Department = max(case when lvl = 3 then val end),
    @Account = max(case when lvl = 4 then val end),
    @SubAccount = max(case when lvl = 5 then val end)
from cte;

-- check the results
select 
    @AccountNo AccountNo, 
    @Company Company, 
    @Location Location, 
    @Department Department, 
    @Account Account, 
    @SubAccount SubAccount
;

Demo on DB Fiddle:

AccountNo     | Company | Location | Department | Account | SubAccount
:------------ | ------: | -------: | ---------: | ------: | ---------:
2-0-200-325-0 |       2 |        0 |        200 |     325 |          0