Substring + Charindex Funktion - aufsplitten eines Wertes

Question

Folgende Bsp. Werte habe ich in einer Spalte (Ret_wert1)

101285/QVA1/112933

3343222222/QBd/8489999

323/UZP6565

2/44U6/8989888

930333/Zid/dddkd33kdkkk

Nun möchte ich diese Werte jeweils wie folgt aufteilen in 4 neue Spalten:

Ret_wert2 = alle Werte vor dem Trennzeichen "/"

Ret_wert3 = alle Werte zwischen dem ersten und dem zweiten "/"

Ret_wert4 = alle Werte nach dem zweiten "/"  --> minus die letzte Ziffer

Ret_wert5 = nur die letzte Ziffer ausgeben, vorausgesetzt es sind zwei Trennstriche vorhanden

In meinem Beispiel würde dies dann folgendermassen getrennt:

101285  QVA1  11293  3

3343222222  QBd  848999 9

323  UZP6565

2  44U6  898988  8

930333  Zid  dddkd33kdkk  k

Kann mir jemand helfen wie die Abfrage lauten muss? Ein select würde reichen

Für die Werte vor dem ersten Trennstrich habe ich folgendes geschrieben:

select substring(ret_wert1,1,charindex( '/', ret_wert1)-1)from ret_infoscore_inkasso_ag_20120410

Vielen Dank jetzt schon für die Hilfe!

Answer 1

Hallo,

um Strings zu splitten gibt es keine vorgefertigte Funtkion in T-SQL. Unter [1] finden Sie jedoch ein Beispiel, wie man diese Funktion realisieren kann.

Alternativ dazu würde es sich anbieten ein kleines Programm in C#/vb.net zu schreiben. Da gibt es die Funktionen split('/') und weitere hilfreiche String-Operationen. Siehe [2]

[1] http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str
[2] http://msdn.microsoft.com/en-us/library/system.string.split(v=vs.100).aspx

Viele Grüße,
Thomas Fröhle
MSDN Hotline für MSDN Online Deutschland

Disclaimer:
Bitte haben Sie Verständnis dafür, dass wir hier auf Rückfragen gar nicht oder nur sehr zeitverzögert antworten können.
Bitte nutzen Sie für Rückfragen oder neue Fragen den telefonischen Weg über die MSDN Hotline: http://www.msdn-online.de/Hotline


Es gelten für die MSDN Hotline und dieses Posting diese Nutzungsbedingungen, Hinweise zu MarkenzeichenInformationen zur Datensicherheit sowie die gesonderten Nutzungsbedingungen für die MSDN Hotline.

Answer 2

declare @ret_wert1 varchar(100), @ret_wert2 varchar(100), @ret_wert3 varchar(100), @ret_wert4 varchar(100), @ret_wert5 varchar(100)

 

set @ret_wert1 = '101285/QVA1/112933'

 

 

select

@ret_wert2 =PARSENAME(replace(@ret_wert1, '/','.'), 1),

@ret_wert3 = PARSENAME(replace(@ret_wert1, '/','.'), 2),

@ret_wert4 = PARSENAME(replace(@ret_wert1, '/','.'), 3)

 

set @ret_wert4 = left(@ret_wert4, len(@ret_wert4)-1)

set @ret_wert5 = case when @ret_wert2 is not null and @ret_wert3 is not null then right(@ret_wert1,1) else null end

 

select @ret_wert2, @ret_wert3, @ret_wert4, @ret_wert5

Answer 3

Danke schon mal.

wenn ich das script ausführe erhalten ich folgendes Resultat:

112933  QVA1  10128  3

Richtig wäre aber:

101285  QVA1  11293  3

Gibt es denn auch eine Möglichkeit, alle Werte auf einmal von ret_wert1 zu splitten? (habe mehrere 100 Datensätze). Sorry, bin noch nicht so vertraut mit der SQL Sprache.

Answer 4

Hallo,

für solche Zwecke habe ich mir mal vor einiger Zeit eine Split Funktion mittels CLR geschrieben.

Hier ist der Quellcode zu finden: http://sqlserverextensions.codeplex.com/

Der Code sehe dann wie folgt aus:

SELECT * FROM DbExt.Split('1,a,r,0,', ',')

Gruß Christian

Answer 5

Ich würde es mit einer UDF machen, die die Felder aufsplittet. Die würde etwa so aussehen:

CREATE FUNCTION getFieldPart 
(
  @Content NVARCHAR(255),
  @FieldPart INT
)
RETURNS NVARCHAR(255)
AS
BEGIN
  DECLARE @P1 NVARCHAR(255)
  DECLARE @P2 NVARCHAR(255)
  DECLARE @P3 NVARCHAR(255)
  DECLARE @P4 NVARCHAR(255)
  DECLARE @S1 INT
  DECLARE @S2 INT
  DECLARE @R  NVARCHAR(255)
  IF @Content IS NULL
    SET @R = NULL
  ELSE
    BEGIN
      SET @S1 = CHARINDEX('/', @Content)
      IF @S1 = 0
        BEGIN
          SET @P1 = @Content
          SET @P2 = NULL
          SET @P3 = NULL
          SET @P4 = NULL
        END
      ELSE
        BEGIN
          SET @P1 = LEFT(@Content, @S1 - 1)
          SET @S2 = CHARINDEX('/', @Content, @S1 + 1)
          IF @S2 = 0 OR @S2 = LEN(@Content)
            BEGIN
              SET @P2 = SUBSTRING(@Content, @S1 + 1, LEN(@Content))
              SET @P3 = NULL
              SET @P4 = NULL
            END
          ELSE
            BEGIN
              SET @P2 = SUBSTRING(@Content, @S1 + 1, @S2 - @S1 - 1)
              SET @P3 = SUBSTRING(@Content, @S2 + 1, LEN(@Content) - @S2 - 1)
              SET @P4 = RIGHT(@Content, 1)
            END
        END
      SET @R = NULL
      IF @FieldPart = 1 AND LEN(COALESCE(@P1, '')) > 0
        SET @R = @P1
      IF @FieldPart = 2 AND LEN(COALESCE(@P2, '')) > 0
        SET @R = @P2
      IF @FieldPart = 3 AND LEN(COALESCE(@P3, '')) > 0
        SET @R = @P3
      IF @FieldPart = 4 AND LEN(COALESCE(@P4, '')) > 0
        SET @R = @P4
    END
  RETURN @R
END

Nun kannst Du die Daten folgendermassen abfragen:

SELECT
  ret_wert1,
  dbo.getFieldPart(ret_wert1, 1) AS ret_wert2,
  dbo.getFieldPart(ret_wert1, 2) AS ret_wert3, 
  dbo.getFieldPart(ret_wert1, 3) AS ret_wert4, 
  dbo.getFieldPart(ret_wert1, 4) AS ret_wert5
FROM ret_infoscore_inkasso_ag_20120410

Oder dann auch, falls Du die aufgeteilten Felder an der Tabelle angefügt hast, eine Update Query laufen lassen, die die Felder initialisiert:

UPDATE ret_infoscore_inkasso_ag_20120410
 SET ret_wert2 = dbo.getFieldPart(ret_wert1, 1)
      , ret_wert3 = dbo.getFieldPart(ret_wert1, 2)
      , ret_wert4 = dbo.getFieldPart(ret_wert1, 3)
      , ret_wert5 = dbo.getFieldPart(ret_wert1, 4)

HTH

Henry

Answer 6

Danke schon mal.

wenn ich das script ausführe erhalten ich folgendes Resultat:

112933  QVA1  10128  3

Richtig wäre aber:

101285  QVA1  11293  3

Gibt es denn auch eine Möglichkeit, alle Werte auf einmal von ret_wert1 zu splitten? (habe mehrere 100 Datensätze). Sorry, bin noch nicht so vertraut mit der SQL Sprache.

create table #tabelle

(

      spalte varchar(255)

)

insert into #tabelle(spalte)

select '101285/QVA1/112933' union

select '3343222222/QBd/8489999' union

select '323/UZP6565' union

select '2/44U6/8989888' union

select '930333/Zid/dddkd33kdkkk'

select      PARSENAME(replace(spalte, '/','.'), 3),

            PARSENAME(replace(spalte, '/','.'), 2),

            left(PARSENAME(replace(spalte, '/','.'), 1),

            len(PARSENAME(replace(spalte, '/','.'), 1))-1),

            case when PARSENAME(replace(spalte, '/','.'), 3) is not null then right(spalte,1) else null end

from #tabelle

Answer 7

@ Henry Habermacher: Die UDF funktioniert pefekt! Vielen Dank. Leider habe ich etwas Mühe die einzelnen Schritte zu verstehen ;)

Den Vorschlag vom Praktikanten erachte ich nicht als ideal, denn ich möchte die einzelnen Werte nicht alle im Script angeben.

Nun habe ich noch eine weitere Variante von einem Arbeitskollegen erhalten.

nach dem ersten Trennzeichen bis zum weiten Trennzeichen:

select substring(ret_wert1,charindex( '/', ret_wert1)+1,
charindex( '/', substring(ret_wert1,charindex( '/', ret_wert1)+1,10))-1)
from ret_infoscore_inkasso_ag_20120410
where len(replace(isnull(Ret_wert1, ''), '/', '/' + ' ')) - len(isnull(ret_wert1, '')) >1

letzte Ziffer:

select Substring(SUBSTRING(ret_wert1, 
charindex( '/', 
substring(ret_wert1,
charindex( '/', ret_wert1)+1,10))+charindex( '/', ret_wert1)+1, 100), LEN(SUBSTRING(ret_wert1, 
charindex( '/', 
substring(ret_wert1,
charindex( '/', ret_wert1)+1,10))+charindex( '/', ret_wert1)+0, 100))-1, 1)
from ret_infoscore_inkasso_ag_20120410
where len(replace(isnull(Ret_wert1, ''), '/', '/' + ' ')) - len(isnull(ret_wert1, '')) >1

Sind diese scripts korrekt? Falls ja, wie würde es aussehen für den Wert vor dem ersten Trennzeichen und wie für den Wert nach dem zweiten Kennzeichen ohne die letzte Ziffer?

Answer 8

Ob diese Scripts korrekt sind, kannst Du doch anhand von Testwerten einfach selber ermitteln. Ich habe zusätzliche Schritte eingebaut für folgende Testwerte:

• Testwert ist NULL
• Testwert hört mit / auf

Des weiteren habe ich zusätzlichen Code eingebaut, der sicherstellt, dass die einzelnen Werte NULL sind, wenn eine leere Zeichenfolge resultiert.

Meine Funktion ist einfacher verständlich, wenn Du Dir der Bedeutung der Variablen bewusst bist. @S1 ist die Position des ersten Separators, @S3 die des zweiten. Falls @S1 0 ist, dann hat es keinen Separator drin. Falls @S2 0 ist, dann ist nur 1 Separator drin. @P1 bis @P4 sind die 4 ermittelten Werte, die dann abhängig davon, welchen dieser 4 Werte Du auslesen willst, in @R geschrieben und als Funktionswert zurückgegeben werden.

Es werden also immer alle 4 Werte ermittelt, unabhängig davon, welchen Du auslesen willst. Erst am Schluss wird dann der gewünschte Wert zurückgeliefert (oder NULL, falls dieser leer ist).

Es gibt viele verschiedene Wege, das zu implementieren. Auch bezüglich Performance könnte man noch einiges herausholen. Wenn z.B. nur der Wert2 benötigt wird, dann müssten die weiteren Werte ja nicht ermittelt werden, was meine Funktion nicht berücksichtigt.

Ich denke, wenn Du eine funktionierende Lösung hast, ist es das beste, diese einfach zu verwenden. Du kannst das auch in eine View einbauen, die Du über die Tabelle legst und die Dir dann in zusätzlichen Feldern alle 4 Werte zurückliefert.

Gruss

Henry

Answer 9

Den Vorschlag vom Praktikanten erachte ich nicht als ideal, denn ich möchte die einzelnen Werte nicht alle im Script angeben.

Die einzelenen Werte sind nur Beispieldaten...

Nur das SELECT-Statement interresiert Dich.

select      PARSENAME(replace(spalte, '/','.'), 3),

            PARSENAME(replace(spalte, '/','.'), 2),

            left(PARSENAME(replace(spalte, '/','.'), 1),

            len(PARSENAME(replace(spalte, '/','.'), 1))-1),

            case when PARSENAME(replace(spalte, '/','.'), 3) is not null then right(spalte,1) else null end

from #Tabelle

#Tabelle und spalte musst Du schon durch Deine eigenen Werte ersetzen...

---

Grüße Ebis

Answer 10

Hallo Ebis

Ich habe dein script nun ausgeführt. Mir ist aufgefallen, dass wenn nur ein Trennzeichen vorkommt, der Wert dann nicht in der korrekten Spalte ausgewiesen wird (siehe Zeile 7+8+10)

Bsp. 

230884/QVA1

2359894/4

577/Qdfs

Diese Werte müssen in der ersten und zweiten Spalte ausgewiesen werden (mit deinem script werden sie in der zweiten und dritten Spalte ausgewiesen)

Der Rest funktioniert sehr gut.

Answer 11

Hallo,

wenn Du bei der Variante Spalte A IS NULL nur jeweils einen nach links verschieben willst, geht das so:

select      CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                 THEN PARSENAME(replace(spalte, '/','.'), 2)
                 ELSE PARSENAME(replace(spalte, '/','.'), 3)
             END AS A,
             CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN left(PARSENAME(replace(spalte, '/','.'), 1), len(PARSENAME(replace(spalte, '/','.'), 1))-1)
                  ELSE PARSENAME(replace(spalte, '/','.'), 2) 
             END AS B,
             CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN NULL
                  ELSE left(PARSENAME(replace(spalte, '/','.'), 1), len(PARSENAME(replace(spalte, '/','.'), 1))-1)
             END AS C,
             CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN right(spalte,1)
                  ELSE 
                      case when PARSENAME(replace(spalte, '/','.'), 3) is not null 
                            then right(spalte,1) 
                            else null 
                      end
             END AS D
from #tabelle

Schöne Grüsse.

Answer 12

Hallo

Ein kleines Problem ist leider immer noch vorhanden. Wenn Spalte A mit "iS NULL" ausgewiesen wird, wird der Wert nun korrekt nach links verschoben, allerdings erhalte ich dann in Spalte D eine "1" bzw. sonst ein Zeichen. In einem solchen Fall darf dorf kein Wert ausgewiesen werden. Siehe mein printscreen

Answer 13

Hallo,

SELECT      CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                 THEN PARSENAME(replace(spalte, '/','.'), 2)
                 ELSE PARSENAME(replace(spalte, '/','.'), 3)
             END AS A,
             CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN left(PARSENAME(replace(spalte, '/','.'), 1), len(PARSENAME(replace(spalte, '/','.'), 1))-1)
                  ELSE PARSENAME(replace(spalte, '/','.'), 2) 
             END AS B,
             CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN NULL
                  ELSE left(PARSENAME(replace(spalte, '/','.'), 1), len(PARSENAME(replace(spalte, '/','.'), 1))-1)
             END AS C,
           CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN NULL
                  ELSE right(spalte,1)
             END AS D
FROM #tabelle

Schönen Abend.

Answer 14

Leider scheint immer noch etwas nicht korrekt zu funktionieren

Bsp. Zeile 156. Originalwert: 671234/SSC1

--> im zweiten Feld wird die "1" abgeschnitten, was nicht korrekt ist

Answer 15

Hallo,

SELECT      CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                 THEN PARSENAME(replace(spalte, '/','.'), 2)
                 ELSE PARSENAME(replace(spalte, '/','.'), 3)
             END AS A,
             CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN PARSENAME(replace(spalte, '/','.'), 1)
                  ELSE PARSENAME(replace(spalte, '/','.'), 2) 
             END AS B,
             CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN NULL
                  ELSE left(PARSENAME(replace(spalte, '/','.'), 1), len(PARSENAME(replace(spalte, '/','.'), 1))-1)
             END AS C,
           CASE WHEN PARSENAME(replace(spalte, '/','.'), 3)IS NULL
                  THEN NULL
                  ELSE right(spalte,1)
             END AS D
FROM #tabelle

Schönen Tag.

Answer 16

der select funktioniert perfekt, vielen Dank!

Ich habe nun versucht, die ausgewiesenen Werte in der gleichen Tabelle zu aktualisieren. Leider werden am Ende neue Zeilen erstellt. Ich möchte die Werte A, B C, D  jeweils in die leeren Felder Ret_wert2, Ret_wert3, Ret_wert4, Ret_wert5 einfügen, jeweils in die vorhandenen Datensätze.

INSERT INTO [infoscore].[dbo].[ret_infoscore_inkasso_ag_20120410]
           ([Ret_wert2]
           ,[Ret_wert3]
           ,[Ret_wert4]
           ,[Ret_wert5])
           
SELECT      CASE WHEN PARSENAME(replace(ret_wert1, '/','.'), 3)IS NULL
                 THEN PARSENAME(replace(ret_wert1, '/','.'), 2)
                 ELSE PARSENAME(replace(ret_wert1, '/','.'), 3)
            END AS A, 
            CASE WHEN PARSENAME(replace(ret_wert1, '/','.'), 3)IS NULL
                  THEN PARSENAME(replace(ret_wert1, '/','.'), 1)
                  ELSE PARSENAME(replace(ret_wert1, '/','.'), 2) 
            END AS B,
            CASE WHEN PARSENAME(replace(ret_wert1, '/','.'), 3)IS NULL
                  THEN NULL
                  ELSE left(PARSENAME(replace(ret_wert1, '/','.'), 1), len(PARSENAME(replace(ret_wert1, '/','.'), 1))-1)
            END AS C,
            CASE WHEN PARSENAME(replace(ret_wert1, '/','.'), 3)IS NULL
                  THEN NULL
                  ELSE right(ret_wert1,1)
            END AS D
FROM [infoscore].[dbo].[ret_infoscore_inkasso_ag_20120410]

Answer 17

ich hoffe es kann mir jemand helfen, danach wäre dieses Thema abgeschlossen.

Danke

Answer 18

Hallo Beni,
wer dieses Forum über die Community Bridge liest, hat nicht gesehen, dass Du deinen letzten Beitrag nachträglich noch mal editiert hast.
Ursprünglich stand dort ja nur :
perfekt, vielen Dank!

Damit war das Thema sicherlich auch für alle die abgeschlossen, die dieses über das Web-FrontEnd verfolgt haben.
Falls Du neue Fragen hast, mache am besten einen neuen Thread auf.

In diesem Falle willst Du kein Insert machen, sondern ein Update, oder?

update [infoscore].[dbo].[ret_infoscore_inkasso_ag_20120410]
                    set [Ret_wert2] =    CASE WHEN PARSENAME(replace(ret_wert1,
'/','.'), 3)IS NULL
                                THEN PARSENAME(replace(ret_wert1, '/','.'), 2)
                                ELSE PARSENAME(replace(ret_wert1, '/','.'), 3)
                     END
                    ,[Ret_wert3] = CASE WHEN PARSENAME(replace(ret_wert1, '/','.'),
3)IS NULL
                                 THEN PARSENAME(replace(ret_wert1, '/','.'), 1)
                                 ELSE PARSENAME(replace(ret_wert1, '/','.'), 2)
                     END
                    ,[Ret_wert4] = CASE WHEN PARSENAME(replace(ret_wert1, '/','.'),
3)IS NULL
                                 THEN NULL
                                 ELSE left(PARSENAME(replace(ret_wert1, '/','.'), 1),
len(PARSENAME(replace(ret_wert1, '/','.'), 1))-1)
                     END
                    ,[Ret_wert5] = CASE WHEN PARSENAME(replace(ret_wert1, '/','.'),
3)IS NULL
                                 THEN NULL
                                 ELSE right(ret_wert1,1)
                     END;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 19

Genau dieses update habe ich gebraucht, vielen Dank!

Ich habe es bemerkt, dass mein edit nichts gebracht hat ;) Nun weiss ich Bescheid

Gruss Beni J.