Split the sring value into 2 different values in sql server

  • kbhanu15

    SSCarpal Tunnel

    Points: 4411

    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

  • Thom A

    SSC Guru

    Points: 98720

    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.

  • This was removed by the editor as SPAM

  • kbhanu15

    SSCarpal Tunnel

    Points: 4411

    Thanks you so much.

  • DesNorton

    SSC-Insane

    Points: 23283

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

  • ben.brugman

    SSChampion

    Points: 13350

    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

     

     

     

     

     

  • Thom A

    SSC Guru

    Points: 98720

    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.

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

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