Splitting string in SQL Server 2008 or 2012

Question

I have table named HR with column called Details which has following data in the format as shown below example

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

the table will update daily with records ,I need these records to be extracted and insert into new table where there are all mentioned fields exists based on date add,means no need to be update old records

Table name is HR_2015Records. The field names are Name,Age,Job,Job location

kindly help me to achieve this

Answer 1

simple way is by using Choose, Substring, Replace and Charindex string Function. Try this.

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 substring(choose(1,'+@str+'),charindex(''='',choose(1,'+@str+'))+1,len(choose(1,'+@str+'))) as Name,
                  substring(choose(2,'+@str+'),charindex(''='',choose(2,'+@str+'))+1,len(choose(2,'+@str+'))) as Age,
                  substring(choose(3,'+@str+'),charindex(''='',choose(3,'+@str+'))+1,len(choose(3,'+@str+'))) as Job,
                  substring(choose(4,'+@str+'),charindex(''='',choose(4,'+@str+'))+1,len(choose(4,'+@str+'))) as [Job Location],
                  substring(choose(5,'+@str+'),charindex(''='',choose(5,'+@str+'))+1,len(choose(5,'+@str+'))) as [Add_Date]'

--print @sql
EXEC Sp_executesql @sql 

Result

Name    Age Job             Job Location  Add_Date
----    --- -------------   ------------  ----------
Jhon    36  Sales Manager   Texas         09/24/2009

Answer 2

I answered the "same" question earlier today. SQL Server 2008 R2 - How to split my varchar column string and get 3rd index string

Here's a function that will split a string inline....

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

Here's a table valued function that will return a table of values

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

Using the first function, you can return the data in the format you want like this...

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

OR using a combination of both functions, you can return the data like this.

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

I can't say which will be quicker, you'll need to check in your environment. I can say that performance won't be great.

Hope that helps