Split de l'Adresse de colonne dans des colonnes distinctes en mode SQL

J'ai une Adresse de colonne dans une table que j'ai besoin de le diviser en plusieurs colonnes dans une vue dans SQL Server 2005. J'ai besoin de diviser la colonne sur le caractère de saut de ligne, chr(10), et il pourrait être de 1 à 4 lignes (de 0 à 3 sauts de ligne) dans la colonne. Ci-dessous, quelques exemples de ce que je dois faire. Quelle est la façon la plus simple de faire cela?

Examples:

Address                 Address1      Address2       Address3            Address4
------------        =   -----------   -----------    -----------------   ---------
My Company              My Company     123 Main St.  Somewhere,NY 12345  
123 Main St.         
Somewhere,NY 12345

Address                 Address1       Address2      Address3      Address4
------------        =   ------------   ----------    -----------   ---------
123 Main St.            123 Main St.
Les données sont pas stockées dans une seule colonne?
Toutes les lignes d'adresse sont stockées dans une seule colonne.

OriginalL'auteur Jamie | 2010-02-05

  1. 3

    cela va diviser l'adresse à l'aide de la parsename de la fonction et de la combiner avec FUSIONNENT pour saisir la bonne info dans la colonne appropriée

    si vous avez plus de 4 lignes, cette méthode ne fonctionnera PAS

    edit: ajouté le code pour inverser l'ordre

        create table #test (address varchar(1000))
--test data
insert #test values('My Company
123 Main St.         
Somewhere,NY 12345')
insert #test values('My Company2
666 Main St.  
Bla Bla       
Somewhere,NY 12345')
insert #test values('My Company2')
--split happens here
select
replace(parsename(address,ParseLen +1),'^','') as Address1,
replace(parsename(address,ParseLen ),'^','') as Address2,
replace(parsename(address,ParseLen -1),'^','') as Address3,
replace(parsename(address,ParseLen -2),'^','') as Address4
from(
select case  ascii(right(address,1)) when 10 then
replace(replace(left(address,(len(address)-1)),'.','^'),char(10),'.')  
else 
replace(replace(address,'.','^'),char(10),'.') end as address,
case  ascii(right(address,1)) when 10 then
len(replace(replace(address,'.','^'),char(10),'.')) -
len(replace(replace(address,'.','^'),char(10),'')) -1
else
len(replace(replace(address,'.','^'),char(10),'.')) -
len(replace(replace(address,'.','^'),char(10),'')) end as ParseLen
from #test) x
    Ce qui fait un bon travail de l'analyse des pièces à part, mais la parsename fonction remplit son tableau dans l'ordre inverse. Donc, si vous avez quelque chose comme 123.456.789 retourne 1=789, 2=456 et 3=123. Et si vous avez 123.456 retourne 1=456 et 2=123. Dans ces deux scenarious, j'ai besoin de 1=123, 2=456 et dans le premier exemple 3=789. Vous ne savez pas si c'est clair. Je me sens comme je devrais être capable de faire cela à l'aide de votre fusionnent méthode et d'aller dans l'ordre inverse ou quelque chose, mais je n'arrive pas à obtenir ce droit.
    ajouté le code pour inverser l'ordre
    Ok, il est presque là. Le seul problème que je vois, c'est maintenant que se retourne la valeur NULL pour tous les quatre champs si il y a un saut de ligne à la fin de la source de champ. En d'autres mots, il y a un vide de la dernière ligne. Est-il une manière pour nous de le nettoyer tous les retours à la ligne et/ou des espaces à la fin qui pourrait être jeter? Merci à tous pour votre aide SQLMenace!
    Jamie, avez-vous essayé la solution que j'ai posté? Il faut juste traiter quoi que ce soit après une 4ème saut de ligne en plus de données sur la 4e ligne (cependant cela indique encore un autre problème d'intégrité des données de votre solution).
    Aaron, j'ai essayé votre solution et le résultat est exactement ce dont j'ai besoin. Le seul problème est la vitesse. Je n'ai pas parlé de l'exécution dans mon post original, mais il a besoin pour s'exécuter le plus rapidement possible. La solution ci-dessus s'exécute deux fois plus rapide que celui que vous avez posté, mais votre sortie semble être sur place. Voyez-vous une façon d'optimiser votre solution. Merci beaucoup pour votre aide.

    OriginalL'auteur SQLMenace

  2. 1

    C'est terriblement méchant... je recommande fortement que si vous voulez traiter chaque ligne de l'adresse séparément, que de les stocker correctement en premier lieu. Au lieu de continuer à faire ce que vous faites, ajouter des colonnes supplémentaires, corriger les données existantes une fois (au lieu de "fixation" à chaque fois que vous exécutez une requête), et ensuite ajuster la procédure stockée qui ne l'insérer /mettre à jour pour qu'il sache utiliser les autres colonnes.

    DECLARE @Address TABLE(id INT IDENTITY(1,1), ad VARCHAR(MAX));
INSERT @Address(ad) SELECT 'line 1
line 2
line 3
line 4'
UNION ALL SELECT 'row 1
row 2
row 3'
UNION ALL SELECT 'address 1
address 2'
UNION ALL SELECT 'only 1 entry here'
UNION ALL SELECT 'let us try 5 lines
line 2
line 3
line 4 
line 5';
SELECT
id,
Line1 = REPLACE(REPLACE(COALESCE(Line1, ''), CHAR(10), ''), CHAR(13), ''),
Line2 = REPLACE(REPLACE(COALESCE(Line2, ''), CHAR(10), ''), CHAR(13), ''),
Line3 = REPLACE(REPLACE(COALESCE(SUBSTRING(Rest, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), Rest), 0), LEN(Rest))), ''), CHAR(10), ''), CHAR(13), ''),
Line4 = REPLACE(REPLACE(COALESCE(SUBSTRING(Rest, NULLIF(CHARINDEX(CHAR(10), Rest) + 1, 1), LEN(Rest)), ''), CHAR(10), ''), CHAR(13), '')
FROM
(
SELECT 
id,
ad,
Line1,
Line2 = SUBSTRING(Rest, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), Rest), 0), LEN(Rest))),
Rest = SUBSTRING(Rest, NULLIF(CHARINDEX(CHAR(10), Rest) + 1, 1), LEN(Rest))
FROM
(
SELECT
id,
ad,
Line1 = SUBSTRING(ad, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), ad), 0), LEN(ad))),
Rest = SUBSTRING(ad, NULLIF(CHARINDEX(CHAR(10), ad) + 1, 1), LEN(ad))
FROM
@address
) AS x
) AS y
ORDER BY id;

    Denis PARSENAME() truc est beaucoup plus propre, bien sûr, mais vous devez être extrêmement prudent sur l'utilisation d'un caractère de remplacement qui est vraiment impossible à apparaître dans les données de façon naturelle. L'accent circonflexe (^) est probablement une bonne idée, mais comme je l'ai dit, vous devez être prudent.

    Il y a aussi des paquets de logiciel qui sont vraiment bonnes à frotter l'adresse et d'autres données démographiques. Mais le nettoyage de l'entrée de données est la chose la plus importante ici que je vais continuer à insister... si chaque ligne de l'adresse doit être traitée séparément, puis de les stocker de cette façon.

    Je suis absolument d'accord à 100%, mais dans ce cas particulier, je n'ai pas de contrôle sur la structure des données. C'est frustrant, mais c'est ce qu'il est.
    Eh bien, tant que les utilisateurs sont prêts à attendre pour la vue d'effectuer cette séparation à chaque fois que vous exécutez une requête... alors je suppose que vous avez raison, c'est ce qu'il est (de merde).

    OriginalL'auteur Aaron Bertrand

  3. 0

    L'analyse du texte dans SQL n'est pas amusant. Si je devais faire quelque chose comme ça, je serais à l'exportation de la colonne d'un fichier texte csv et de l'analyser dans le langage de script comme Perl/PHP/Python. De cette façon, je peux profiter de construit dans les fonctions de chaîne et d'expression régulière de langage de script.

    OriginalL'auteur Yada