SQL Server - CHARINDEX sur les valeurs ASCII

Question

J'ai une colonne dans une table SQL Server qui a une valeur longue avec des sauts de ligne. J'essaie de trouver les valeurs avant chaque saut de ligne.

EXEMPLE :

--Column name: ItemDescription

Case Qty: 12
Weight: 8 oz.
Flavor code: PB
Size: STOCK
Cut: 1/8" x 1/8" x 3/16"
Additions: Bells
Cover Brine #: P1
Kosher Cert: OU
Organic Cert: 

C'est EXACTEMENT ce que j'obtiens lorsque je copie la cellule de mes résultats et que je la colle. J'ai donc converti ce champ en VARBINARY et a vu ce que ASCII Les codes sont ici. Voici une partie de l'interprétation ASCII d'une valeur :

43 61 73 65 20 51 74 79 3A 20 31 32 0D0A 57 65 69 67 68 74 3A 20 38 20 6F 7A 2E 0D0A 46

0D0A signifie retour chariot et saut de ligne.

RÉSULTAT PRÉFÉRÉ :

Maintenant que les données sont claires, j'essaie de trouver la valeur après les deux points et avant le saut de ligne et de la placer dans une nouvelle colonne.

Voici donc à quoi devrait ressembler mon résultat préféré :

CE QUE J'AI ESSAYÉ :

Voici ma requête SQL actuelle pour ce faire :

DECLARE @firstLine int
DECLARE @secondLine int
DECLARE @thirdLine int
DECLARE @fourthLine int
DECLARE @fifthLine int
DECLARE @sixthLine int
DECLARE @seventhLine int
DECLARE @eighthLine int

DECLARE @firstColon int
DECLARE @secondColon int
DECLARE @thirdColon int
DECLARE @fourthColon int
DECLARE @fifthColon int
DECLARE @sixthColon int
DECLARE @seventhColon int
DECLARE @eighthColon int
DECLARE @ninethColon int
DECLARE @itemDesc varchar(MAX)

SELECT 
    @itemDesc = ItemDescription
    ,@firstLine = CHARINDEX(CHAR(13), ItemDescription, 1)
    ,@secondLine = CHARINDEX(CHAR(13), ItemDescription, @firstLine + 1)
    ,@thirdLine =  CHARINDEX(CHAR(13), ItemDescription, @secondLine + 1)
    ,@fourthLine =  CHARINDEX(CHAR(13), ItemDescription, @thirdLine + 1)
    ,@fifthLine =  CHARINDEX(CHAR(13), ItemDescription, @fourthLine+ 1)
    ,@sixthLine =  CHARINDEX(CHAR(13), ItemDescription, @fifthLine + 1)
    ,@seventhLine =  CHARINDEX(CHAR(13), ItemDescription, @sixthLine + 1)
    ,@eighthLine = CHARINDEX(CHAR(13), ItemDescription, @seventhLine + 1)

    ,@firstColon = CHARINDEX(CHAR(58), ItemDescription, 1)--aaa
    ,@secondColon = CHARINDEX(CHAR(58), ItemDescription, @firstLine + 1)
    ,@thirdColon = CHARINDEX(CHAR(58), ItemDescription, @secondLine + 1)
    ,@fourthColon = CHARINDEX(CHAR(58), ItemDescription, @thirdLine + 1)
    ,@fifthColon = CHARINDEX(CHAR(58), ItemDescription, @fourthLine + 1)
    ,@sixthColon = CHARINDEX(CHAR(58), ItemDescription, @fifthLine + 1)
    ,@seventhColon = CHARINDEX(CHAR(58), ItemDescription, @sixthLine + 1)
    ,@eighthColon = CHARINDEX(CHAR(58), ItemDescription, @seventhLine + 1)
    ,@ninethColon = CHARINDEX(CHAR(58), ItemDescription, @eighthLine + 1)
FROM TableName

SELECT 
    ItemDescription
    ,CONVERT(VarBInary, itemDescription)
    ,LTRIM(SUBSTRING(ItemDescription, @firstColon + 2, @firstLine - (@firstColon - 1))) as caseQty          --1
    ,LTRIM(SUBSTRING(ItemDescription, @secondColon + 2, @secondLine - (@secondColon - 1))) as caseQty           --2
    ,LTRIM(SUBSTRING(ItemDescription, @thirdColon + 2, @thirdLine - (@thirdColon - 1))) as FlavorCode
    ,LTRIM(SUBSTRING(ItemDescription, @fourthColon + 2, @fourthLine - (@fourthColon - 1))) as Size
    ,LTRIM(SUBSTRING(ItemDescription, @fifthColon + 2, @fifthLine - (@fifthColon - 1))) as Cut
    ,LTRIM(SUBSTRING(ItemDescription, @sixthColon + 2, @sixthLine - (@sixthColon - 1))) as Additions
    ,LTRIM(SUBSTRING(ItemDescription, @eighthColon + 2, @eighthLine- (@eighthColon - 1))) as Brine
FROM
    TableName

LA QUESTION :

Pour une raison quelconque, le SUBSTRING n'obtient pas la bonne sous-chaîne ! J'obtiens le Qty y Weight correctement. Mais si la taille est RELISHSTOCK j'obtiens RELISHSTOC . Pour FlavorCode j'obtiens ut: (sous-chaîne de "Cut :"). Pour Cut j'obtiens 8" x 3/ . Pour Additions j'obtiens Brin (Sous-chaîne de "Cover Brine").

QU'EST-CE QUE JE FAIS MAL ! ! J'ai essayé plusieurs combinaisons différentes de la longueur de la sous-chaîne. Je sais pertinemment que c'est la longueur. Mais maintenant que je me rends compte que toutes les positions de départ après le code de saveur est foutu, je ne suis pas trop sûr où l'erreur est.

Toute aide serait grandement appréciée.

Gracias.

Answer 1

Après environ une semaine à me débattre avec ce problème, et une semaine à essayer d'adapter mon idée d'exécution aux suggestions mentionnées dans les commentaires, j'ai fini par trouver une solution à ce problème.

Étape 1 :
J'ai découvert qu'il était plus facile de manipuler un caractère spécial comme ! qu'il ne l'était pour le saut de ligne et le retour à la ligne ( Char(10) y Char(13) ).
Donc, la première chose que j'ai faite a été de remplacer tous les caractères de saut de ligne par ! . Comme ça :

REPLACE(REPLACE(columnName, char(10), ''), char(13), '!')

Cela permet de s'assurer que le ! est toujours précédé d'un espace, ce qui le rend plus facile à gérer.

Étape 2 :
J'ai trouvé une réponse sur SO qui avait une fonction table-valeur pour diviser une chaîne de caractères. Le nom de la fonction est fnSplitString et il prend deux paramètres : @string nvarchar(MAX) y @delimited char(1) .

ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(columnName NVARCHAR(MAX), outValue NVARCHAR(MAX)) 
BEGIN 
    DECLARE 
        @start INT
        ,@end INT
        ,@colon INT

    SELECT 
        @start = 1
        ,@colon = CHARINDEX(@delimiter, @string)
        ,@end = CHARINDEX(CHAR(33), @string)
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF(SUBSTRING(@string, @colon + 1, 1) = ' ')
        BEGIN
            INSERT INTO 
                @output (columnName, outValue)  
            VALUES
                ((SUBSTRING(@string, @start, @colon - @start)) ,(SUBSTRING(@string, @colon + 2,  @end - @colon - 2)))
            SET @start = @end + 1 
            SET @colon = CHARINDEX(@delimiter, @string, @start)
            SET @end = CHARINDEX(CHAR(33), @string, @start)
        END
        ELSE
        BEGIN
            INSERT INTO 
                @output (columnName, outValue)  
            VALUES
                ((SUBSTRING(@string, @start, @colon - @start)) ,(SUBSTRING(@string, @colon + 1,  @end - @colon - 1)))
            SET @start = @end + 1 
            SET @colon = CHARINDEX(@delimiter, @string, @start)
            SET @end = CHARINDEX(CHAR(33), @string, @start)
        END
    END 
    RETURN 
END

J'ai dû modifier beaucoup de choses dans la fonction parce que cette fonction était initialement destinée à obtenir des valeurs APRÈS un délimiteur jusqu'à la fin de la chaîne, ce qui est assez simple. Mais la mienne devait aller du délimiteur jusqu'à l'élément ! qui est char(33) .

Étape 3 :
J'ai écrit une procédure stockée qui gère toutes ces opérations. J'avais déjà une procédure stockée assez longue en place pour mon besoin et je devais ajouter les résultats de cette fonction joints aux résultats d'une instruction select à un nouveau CTE et l'utiliser dans le jeu de résultats final.

Pour ceux qui sont curieux, j'ai trouvé dans une autre question sur l'OS que la jointure d'une table et d'une fonction peut se faire de la manière suivante :

SELECT
    table1.columnName1
    ,function1.columnName2
    ,function1.columnName3
FROM
    tableName1 table1
CROSS APPLY
    dbo.functionName1(table1.columnName2, ':') function1

Ça a marché.

J'espère que cela aidera quelqu'un à l'avenir.