Split values in the column

  • Hello All,

    I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:

    USE tempdb;

    GO

    DECLARE @TEST_DATA TABLE

    (

    DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

    , InvNoVARCHAR(10) NOT NULL

    , SalesCode NCHAR(80) NOT NULL

    , Amount NCHAR(80) NOT NULL

    );

    INSERT INTO @TEST_DATA (InvNo, SalesCode, Amount)

    VALUES

    ('20001', '080!124', '25.36!6.34')

    ,('20002', '124!169', '136.55!68.28')

    ,('20003', '125!095', '174.48!43.62')

    ,('20004', '165!166', '138.00!34.50')

    ;

    SELECT

    J.DT_ID

    ,J.InvNo

    ,J.SalesCode

    ,J.Amount

    FROM @TEST_DATA J

  • You could use the DelimitedSplit8k found in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

    SELECT

    J.DT_ID

    ,J.InvNo

    ,sc.Item AS SalesCode

    ,a.Item AS Amount

    FROM @TEST_DATA J

    CROSS APPLY dbo.DelimitedSplit8K( J.SalesCode, '!') sc

    CROSS APPLY dbo.DelimitedSplit8K( J.Amount, '!') a

    WHERE sc.ItemNumber = a.ItemNumber

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • You can also do this:

    SELECT

    J.DT_ID

    ,J.InvNo

    ,Salesmman = SUBSTRING(J.SalesCode,1,CHARINDEX('!',J.SalesCode)-1)

    ,Commision = SUBSTRING(J.Amount,1,CHARINDEX('!',J.Amount)-1)

    FROM @TEST_DATA J

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • All-I appreciate your response. In the salescode column there is single salescode also. How do I handle it? Here is the updated DDL. Is there a way to create 2 rows if there are two salescode (080!124) and single row if there is single salescode (080)?

    Again, thank you for your response.

    DECLARE @TEST_DATA TABLE

    (

    DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,

    InvNo VARCHAR(10) NOT NULL ,

    SalesCode NCHAR(80) NOT NULL ,

    Amount NCHAR(80) NOT NULL

    );

    INSERT INTO @TEST_DATA (InvNo, SalesCode, Amount)

    VALUES

    ('20001', '080!124', '25.36!6.34') ,

    ('20002', '124!169', '136.55!68.28') ,

    ('20003', '125!095', '174.48!43.62') ,

    ('20004', '165!166', '138.00!34.50'),

    ('20005', '124', '125.25'),

    ('20006', '080', '25')

    ;

    SELECT

    J.DT_ID

    ,J.InvNo

    ,J.SalesCode

    ,J.Amount

    FROM @TEST_DATA J

  • Use Luis's suggestion, it will work for single valued entities

  • anthony- I tried using Luis's code and got below error message. Any inputs on what I may be doing wrong. Thanks.

    Msg 208, Level 16, State 1, Line 19

    Invalid object name 'tempdb.dbo.DelimitedSplit8K'.

  • The splitter function doesn't exist in TempDB.

    Not ideal creating the function in TempDB as after a restart of SQL the function will be missing, unless you create it in MODEL also but then any new database you create will also get the function.

    Create the function in a userdatabase and run your query in that databases context

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply