Avatar billede ulykken-smed Juniormester
29. oktober 2021 - 13:19 Der er 9 kommentarer og
1 løsning

Hjælp til String_Split

Hej
Jeg er ny i SQL og er i gang med at skulle splitte nogle linjer i ms sql server management studio, jeg håber at der er nogle der har et eksempel på hvordan jeg splitter en string til flere rækker hver gang der er et mellemrum

Mit eksempel:


Data nu       
nummer    Navn    Dato
1            Hans    RH 100521 - 120521
2            Per            RH 100521 - 120521
3            Hans    RO 100521
4            Pia            SLA 100520 - 120520
       
       
Data efter split       
nummer    Navn    Dato
1            Hans    RH
1            Hans    100521
1            Hans    -
1            Hans    120521
2            Per            RH
2            Per            100521
2            Per        -
2            Per            120521
3            Hans    RO
3            Hans    100521
4            Pia            SLA
4            Pia            100520
4            Pia            -
4            Pia              120520
Avatar billede erikjacobsen Ekspert
29. oktober 2021 - 13:43 #1
Noget i denne stil?  Lille tabel

create table split ( id int, t varchar(255))
insert into split values (1,'Hans Petersen 2020-02-02')
insert into split values (2,'Jens Jensen 2021-01-01')

og så: 

select * from split outer apply string_split(split.t,' ')

Der giver

1    Hans Petersen 2020-02-02    Hans
1    Hans Petersen 2020-02-02    Petersen
1    Hans Petersen 2020-02-02    2020-02-02
2    Jens Jensen 2021-01-01    Jens
2    Jens Jensen 2021-01-01    Jensen
2    Jens Jensen 2021-01-01    2021-01-01
Avatar billede erikjacobsen Ekspert
29. oktober 2021 - 13:49 #2
Men ... det er ikke smart at være nødt til at splitte strenge op i SQL. Kan du lave strukturen om, så "RH" og "100521" osv har egne kolonner, så bliver livet meget nemmere, og SQL-maskinen kan optimere forespørgslerne.

Nuvel, STRING_SPLIT ser ud til at virke, men i https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15  skriver de blandt andet:

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string.

Du har formentlig brug for at rækkefølgen bevares - det kan du så ikke være sikker på. Måske er der altid den korrekte rækkefølge for små eksempler, men det går galt når der er mange data.

Derfor: egentlig er dette et problem der bør løses i det overliggende almindelige programmeringssprog.
Avatar billede ulykken-smed Juniormester
29. oktober 2021 - 14:00 #3
Hej Erik
Tak for det hurtige svar.

Jeg har en meget stor tabel som jeg skal løbe igennem, og normalt ville jeg lave det i excel og vba, men det bliver langt over 1 milion rækker, så derfor prøver jeg med SQL, så det skal helst være noget jeg kan kører på det data sæt
Avatar billede erikjacobsen Ekspert
29. oktober 2021 - 14:13 #4
#3: I den størrelsesorden kan du sagtens få problemer med rækkefølgen. Det plejer man at løse med en ORDER BY, men jeg kan ikke se hvordan man skulle kunne gøre det med resultatet fra STRING_SPLIT.
Avatar billede ulykken-smed Juniormester
29. oktober 2021 - 14:43 #5
Jeg tænkte at det var et forsøg værd, men tak for hjælpen
Avatar billede arne_v Ekspert
29. oktober 2021 - 16:00 #6
Tabel-strukturen er helt klart forkert i forhold til opgaven.

Men hvis vi nu antager at tabel-strukturen ikke kan laves om. Sådan er det jo typisk.

T-SQL er næppe det rette sprog til opgaven.

Jeg ville lave en store procedure i C# som hentede alle rækker fra tabellen of returnerede query rækker som du vil have dem. I C# vil du have fuld kontrol over både split og eventuel logik.

Poblemet er at det kræver privs at installere en CLR SP.
Avatar billede arne_v Ekspert
29. oktober 2021 - 17:54 #7

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;

namespace SQLSrvLib
{
    public class MySP1
    {
        [SqlProcedure]
        public static void MySP()
        {
            SqlDataRecord rec = new SqlDataRecord(new SqlMetaData("nummer", SqlDbType.Int), new SqlMetaData("navn", SqlDbType.VarChar, 10), new SqlMetaData("dato", SqlDbType.VarChar, 6));
            SqlContext.Pipe.SendResultsStart(rec);
            using(SqlConnection con = new SqlConnection("context connection=true")) 
            { 
                con.Open();
                using(SqlCommand sel = new SqlCommand("SELECT nummer, navn, dato FROM split", con))
                {
                    using(SqlDataReader rdr = sel.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            int nummer = (int)rdr["nummer"];
                            string navn = (string)rdr["navn"];
                            string dato = (string)rdr["dato"];
                            foreach(string datopart in dato.Split(' '))
                            {
                                rec.SetInt32(0, nummer);
                                rec.SetString(1, navn);
                                rec.SetString(2, datopart);
                                SqlContext.Pipe.SendResultsRow(rec);
                            }
                        }
                    }
                }
            }
            SqlContext.Pipe.SendResultsEnd();   
        }
    }
}



1>
2>
3> CREATE ASSEMBLY MySP1 FROM 'C:\Work\clrsp\MySP1.dll' WITH PERMISSION_SET = SAFE;
4> GO
1> CREATE PROCEDURE MySP
2> AS
3> EXTERNAL NAME MySP1.[SQLSrvLib.MySP1].MySP;
4> GO
1> CREATE TABLE split (
2>    nummer INTEGER,
3>    navn VARCHAR(10),
4>    dato VARCHAR(32),
5>    PRIMARY KEY(nummer)
6> )
7> GO
1> INSERT INTO split VALUES(1, 'Hans', 'RH 100521 - 120521')
2> INSERT INTO split VALUES(2, 'Per', 'RH 100521 - 120521')
3> INSERT INTO split VALUES(3, 'Hans', 'RO 100521')
4> INSERT INTO split VALUES(4, 'Pia', 'SLA 100520 - 120520')
5> GO
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1> SELECT * FROM split
2> GO
nummer      navn      dato
----------- ---------- --------------------------------
          1 Hans      RH 100521 - 120521
          2 Per        RH 100521 - 120521
          3 Hans      RO 100521
          4 Pia        SLA 100520 - 120520

(4 rows affected)
1> EXEC MySP
2> GO
nummer      navn      dato
----------- ---------- ------
          1 Hans      RH
          1 Hans      100521
          1 Hans      -
          1 Hans      120521
          2 Per        RH
          2 Per        100521
          2 Per        -
          2 Per        120521
          3 Hans      RO
          3 Hans      100521
          4 Pia        SLA
          4 Pia        100520
          4 Pia        -
          4 Pia        120520

(14 rows affected)
1> DROP TABLE split
2> GO
1> DROP PROCEDURE MySP
2> GO
1> DROP ASSEMBLY MySP1
2> GO
Avatar billede erikjacobsen Ekspert
29. oktober 2021 - 18:11 #8
For god ordens skyld bør resultatet have yderligere en kolonne, der viser rækkefølgen efter split. Trivielt at lave i Arnes eksempel. F.eks startende ved 1 for hver person

          1 Hans      RH        1
          1 Hans      100521  2
          1 Hans      -          3
          1 Hans      120521  4
Avatar billede arne_v Ekspert
29. oktober 2021 - 19:00 #9
Ja. Det er fordelen ved at kode logikken i C#. Det er nemt at sætte en tæller på. Det er nemt at erstatte bindestreg med alle de mellemliggende datoer etc. fordi man har frie hænder til at gøre hvad man vil.
Avatar billede ulykken-smed Juniormester
30. oktober 2021 - 18:21 #10
Hej Arne
Tak for hjælpen
Det var en stor opgave jeg fik sat i gang, så nu vil jeg bruge nogle aftner på at få det til at køre.
Tak
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester