J'ai une table nommée HR avec une colonne appelée Details qui a datatables suivantes dans le format comme indiqué ci-dessous exemple
Name=Jhon|Age=36|Job=Sales Manager|Job Location=Texas|Add_Date:09/24/2009 Name=Tom|Age=27|Job=Sales Man|Job Location=Texas|Add_Date:07/19/2014 Name=Ferdinan|Age=38|Job=Sales Man|Job Location=Texas|Add_Date:12/24/2014 Name=Jhonson|Age=29|Job=Marketing Manager|Job Location=Texas|Add_Date:12/26/2014 Name=Mikel|Age=26|Job=Technician|Job Location=Texas|Add_Date:12/27/2014 Name=Steve|Age=25|Job=Technician|Job Location=Los Angeles|Add_Date:12/27/2014 Name=Rob|Age=29|Job=Programmer|Job Location=NC|Add_Date:12/28/2014
la table sera mise à jour quotidiennement avec des loggings, j'ai besoin de ces loggings à extraire et insert dans une nouvelle table où il y a tous les champs mentionnés existe en fonction de la date append, signifie pas besoin de mettre à jour les anciens loggings
Le nom de la table est HR_2015Records
. Les noms de champs sont Name
, Age
, Job
, Job location
bien vouloir m'aider à réaliser cela
J'ai répondu à la «même» question plus tôt aujourd'hui. SQL Server 2008 R2 – Comment split ma string de colonne varchar et get la 3ème string d'index
Voici une fonction qui va split une string en ligne ….
CREATE FUNCTION [dbo].[FN_SPLIT] ( --SELECT DBO.FN_SPLIT('TEST1 , TEST2', 2, ',') @s varchar(512), @i int, @sep char(1) = ',') RETURNS varchar(512) AS BEGIN DECLARE @Ret VARCHAR(512); WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT @Ret = RTRIM(SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END)) FROM Pieces WHERE pn = @i RETURN @Ret; END
Voici une fonction table valorisée qui returnnera une table de valeurs
CREATE FUNCTION [dbo].[FN_SPLIT_TBL](@InExp varchar(8000), @Sep varchar(10)) --SELECT * FROM DBO.[FN_SPLIT_TBL]('TEST1,TEST2', ',') RETURNS @Res TABLE( Pos int, Value varchar(max)) AS BEGIN WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@Sep, @InExp) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @InExp, stop + 1) FROM Pieces WHERE stop > 0 ) INSERT INTO @Res SELECT pn, SUBSTRING(@InExp, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces OPTION (MAXRECURSION 0); RETURN; END
En utilisant la première fonction, vous pouvez returnner datatables dans le format que vous voulez comme ça …
SELECT dbo.[FN_SPLIT](data.Name, 2, '=') as Name, dbo.[FN_SPLIT](data.Age, 2, '=') as Age, dbo.[FN_SPLIT](data.Job, 2, '=') as Job, dbo.[FN_SPLIT](data.Location, 2, '=') as Location, dbo.[FN_SPLIT](data.Add_Date, 2, '=') as Add_Date FROM ( SELECT dbo.[FN_SPLIT](Details, 1, '|') as Name, dbo.[FN_SPLIT](Details, 2, '|') as Age, dbo.[FN_SPLIT](Details, 3, '|') as Job, dbo.[FN_SPLIT](Details, 4, '|') as Location, dbo.[FN_SPLIT](Details, 5, '|') as Add_Date FROM HR) data
OU en utilisant une combinaison des deux fonctions, vous pouvez returnner datatables comme ceci.
SELECT dbo.[FN_SPLIT](Name.Value, 2, '=') as Name, dbo.[FN_SPLIT](Age.Value, 2, '=') as Age, dbo.[FN_SPLIT](Job.Value, 2, '=') as Job, dbo.[FN_SPLIT](Location.Value, 2, '=') as Location, dbo.[FN_SPLIT](Add_Date.Value, 2, '=') as Add_Date FROM HR data CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Name CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Age CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Job CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Location CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Add_Date WHERE Name.Pos = 1 AND Age.Pos = 2 AND Job.Pos = 3 AND Location.Pos = 4 AND Add_Date.Pos = 5
Je ne peux pas dire lequel sera le plus rapide, vous devrez vérifier dans votre environnement. Je peux dire que la performance ne sera pas géniale.
J'espère que cela pourra aider
façon simple est en utilisant Choose, Subssortingng, Replace and Charindex
string Fonction. Essaye ça.
DECLARE @str VARCHAR(1000)='Name=Jhon|Age=36|Job=Sales Manager|Job Location=Texas|Add_Date:09/24/2009', @sql NVARCHAR(max) SET @str = ''''+ Replace(Replace(@str, '|', ''','''), ':', '=')+ '''' SET @sql= 'select subssortingng(choose(1,'+@str+'),charindex(''='',choose(1,'+@str+'))+1,len(choose(1,'+@str+'))) as Name, subssortingng(choose(2,'+@str+'),charindex(''='',choose(2,'+@str+'))+1,len(choose(2,'+@str+'))) as Age, subssortingng(choose(3,'+@str+'),charindex(''='',choose(3,'+@str+'))+1,len(choose(3,'+@str+'))) as Job, subssortingng(choose(4,'+@str+'),charindex(''='',choose(4,'+@str+'))+1,len(choose(4,'+@str+'))) as [Job Location], subssortingng(choose(5,'+@str+'),charindex(''='',choose(5,'+@str+'))+1,len(choose(5,'+@str+'))) as [Add_Date]' --print @sql EXEC Sp_executesql @sql
Résultat
Name Age Job Job Location Add_Date ---- --- ------------- ------------ ---------- Jhon 36 Sales Manager Texas 09/24/2009