Post reply

Split the sring value into 2 different values in sql server

  • May 30, 2019 at 10:38 am

    #3648525

    Hi Team,

    my existing data like below:

    create table #test

    (

    type1 varchar(100)

    )

    insert into #test

    select '-500|-1'

    UNION ALL

    select '-400|31' UNION ALL

    select '0|31' UNION ALL

    select '36|31' UNION ALL

    select '40|31' union ALL

    select '41|31' union ALL

    select '62|31' UNION ALL

    select '63|31' UNION ALL

    select '163|31'

    select * from #test

    -500|-1

    -400|31

    0|31

    36|31

    40|31

    41|31

    62|31

    63|31

    163|31

    185|31

    187|31

    192|31

     

    Expected Data:

    debtTypeId      ratingTypeId

    31                      0

    31                      36

    31                     40

    31                     41

    31                    62

    31                   63

    31                  163

    31                  185

    31                   187

    31                   192

    Thanks

    Bhanu

  • May 30, 2019 at 11:07 am

    #3648528

    Couple of options using PARSENAME and CHARINDEX:

    SELECT PN.debtTypeId,
           PN.ratingTypeId
    FROM #test T
         CROSS APPLY(VALUES(PARSENAME(REPLACE(T.Type1,'|','.'),1),PARSENAME(REPLACE(T.Type1,'|','.'),2))) PN(debtTypeId,ratingTypeId)
    WHERE PN.debtTypeId = 31
      AND PN.ratingTypeId >= 0;

    SELECT V.debtTypeId,
           V.ratingTypeId
    FROM #test T
         CROSS APPLY (VALUES(CHARINDEX('|',T.type1)))CI(I)
         CROSS APPLY (VALUES(STUFF(T.type1,1,CI.I,''),LEFT(T.type1,CI.I - 1))) V(debtTypeId,ratingTypeId)
    WHERE V.debtTypeId = 31
      AND V.ratingTypeId >= 0;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 30, 2019 at 11:11 am

    This was removed by the editor as SPAM

  • May 30, 2019 at 11:11 am

    #3648530

    Thanks you so much.

  • May 30, 2019 at 11:45 am

    #3648536

    Since you appear to be using SQL 2016, you can also look at the new STRING_SPLIT function

    How to post data/code on a forum to get the best help.

  • May 31, 2019 at 9:59 am

    #3648913

    The usefull function :

    DelimitedSplit8K

    Has the advantage of numbered items above the STRING_SPLIT function. The DelimitedSplit8k gives the item number and the item value, so you have them ordered.  Also it is tuned to be fast. You can find it on the sqlsevercentral website.

    See the thread below for some links:

    https://www.sqlservercentral.com/forums/topic/delimitedsplit8k-versus-delimitedsplit_long

    The routine is ready to use, and once installed is very usefull for a lot of situations. For your situation this tool is maybe oversized, but once you get used to this tool it is powerfull and also usable for 'small scale' jobs.

     

    Ben

     

     

     

     

     

  • May 31, 2019 at 10:07 am

    #3648914

    Considering the OP is using 2016, they have access to DelimitedSplit8k_LEAD too: https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2. I don't really, however, see the point in using a splitter when there are only 2 values though; especially as using the splitter will unpivot the data, and then you'll have to pivot it back.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

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

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