Solved

MSSQL: Query to convert field in a row into several rows

Posted on 2016-09-23
29
84 Views
Last Modified: 2016-10-22
Hi,
hope everybody is ok.
Well, I need to fix an issue in an important table of our database. The scenario is the following:

We have a table for Customers ("tblCustomer"), it has several columns for all customers details. Last one column is named 'Log' where we save all the updates made to each customer, the issue is that the design of this table was wrong because this field is concatenating all update details in this very same field, so today... We have hundreds of replicated data in this field so when the asp.net page wants to run a query to update all customers of this table the memory hangs (today it takes more than 2 days to make an update).

Convention:
When I say "Log updates" I am referring to every update the user does to any "tblCustomer" rows.

A solution I am trying to do is:
  1. Modify the asp.net page that updates the "tblCustomer" table
[list=2]Create a new table called "tblCustomerHistory" so we save a history of the updates in this table. This table would have 2 columns "id_Customer" and "Log"[/list]
[list=3]Modify our asp.net page to save only last update in current "Log" column for the "tblCustomer" table.[/list]
[list=4*]Run a query for existing data in 'log' column in "tblCustomer" to move it to "tblCustomerHistory" table in 'log' column.[/list]

* Point 4 is the one I need help with
To get a query that allow me to take current data in "log" column for "tblCustomer" table ("Image_1.PNG") and separate log updates and insert text creating new rows in "tblCustomerHistory" (Image_2.PNG).

Note: log updates to "tblCustomer" table in "Log" column are separated by "<br>" so asp.net can properly display to the user so if in the new table we can have all records with <br> would be a perfect solution, if not possible, I will understand it.

Thanks a lot.Image_2.PNGImage_1.PNG
0
Comment
Question by:José Perez
  • 15
  • 13
29 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41813403
Well you are absolutely right to re-design that table. "log" should never have been a single column and needs to be in a table that joins back to the customers.

It would be great if you could supply sample data (not images). You don't need to give us all columns and the names of clients can be obscured. Just a few columns and a few rows but make sure the log column is included in the sample. Use a "code block" (or an attached text file). Does not need to be a big sample.

In essence you need a "split string" function (and there are many available) that will divide "log" into many rows that you can build a new table from.
e.g. https://www.experts-exchange.com/questions/28779240/SQL-Substring.html#a41125184

But I'd like to see some real samples of "log" if possible.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41813416
With this sample and this function
CREATE TABLE tblCustomer
    ([id_Empresa] int, [log] varchar(250))
;

INSERT INTO tblCustomer
    ([id_Empresa], [log])
VALUES
    (104, '<br>Cambio estado (1) Activo 7/28/2016 12:00:00 AM<br>Cambio estado (1) Activo 7/28/2016 12:00:00 AM<br>Cambio estado (1) Activo 7/28/2016 12:00:00 AM<br>Cambio estado (1) Activo 7/28/2016 12:00:00 AM<br>Cambio estado (1) Activo 7/28/2016 12:00:00 AM')
;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
RETURNS TABLE WITH SCHEMABINDING 
AS
--SELECT * FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values CROSS APPLY DBA.dbo.SplitStringIntoTable(test_values.string, '/')
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
), 
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq,
       LTRIM(RTRIM(SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)))) AS value
FROM cteStart s;

GO

Open in new window

Using this query:
SELECT 
       c.id_Empresa
     , SplitString.value_seq
     , pos1, SplitString.value
     , case when ca2.pos1 > 0 then left(SplitString.value, ca2.pos1 + 6) end left_part
     , case when ca2.pos1 > 0 then substring(SplitString .value, ca2.pos1 + 7, len(SplitString.value)) end right_part
FROM tblCustomer c
CROSS APPLY dbo.SplitStringIntoTable ( replace(c.log,'<br>','~'), '~' ) as SplitString
CROSS APPLY (select charindex('Activo',SplitString.value) as pos1 ) as ca2

Open in new window

I got this result:
| id_Empresa | value_seq | pos1 |                                          value |                 left_part |            right_part |
|------------|-----------|------|------------------------------------------------|---------------------------|-----------------------|
|        104 |         1 |    0 |                                                |                    (null) |                (null) |
|        104 |         2 |   19 | Cambio estado (1) Activo 7/28/2016 12:00:00 AM | Cambio estado (1) Activo  | 7/28/2016 12:00:00 AM |
|        104 |         3 |   19 | Cambio estado (1) Activo 7/28/2016 12:00:00 AM | Cambio estado (1) Activo  | 7/28/2016 12:00:00 AM |
|        104 |         4 |   19 | Cambio estado (1) Activo 7/28/2016 12:00:00 AM | Cambio estado (1) Activo  | 7/28/2016 12:00:00 AM |
|        104 |         5 |   19 | Cambio estado (1) Activo 7/28/2016 12:00:00 AM | Cambio estado (1) Activo  | 7/28/2016 12:00:00 AM |
|        104 |         6 |   19 | Cambio estado (1) Activo 7/28/2016 12:00:00 AM | Cambio estado (1) Activo  | 7/28/2016 12:00:00 AM |
       

Open in new window

some extra columns are sown only to help understand.

Note I have assumed [log] does not contain ~
and replaced <br> with ~
so that the split string function will work

refer: http://sqlfiddle.com/#!6/c9966/1
0
 
LVL 2

Author Comment

by:José Perez
ID: 41835816
I am so sorry I abandoned this question. I can now continue on it, I have done the test and I can now replicate Expert comment.
Can the admin re-activate this question so I can answer?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41836115
The auto-close process was stopped by your comment, so you are nw able to close it.
j
0
 
LVL 2

Author Comment

by:José Perez
ID: 41836125
Many Thanks!

Ok. I have tried the function and query but I don't know how to convert it to do what I need.

Please check point 4 in my question...
The resulting query should insert the displayed results into a table called "tblCustomerHistory".
Can you please update your query so I can re-test it?
Thanks.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41836141
Im on a train heading to work. I may not be able to do this for 12 hours or so.
0
 
LVL 2

Author Comment

by:José Perez
ID: 41836142
ok. I can wait... have a good trip :)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41836146
Have you tried just adding

Insert into tblCustomerHistory


Above the select query?
0
 
LVL 2

Author Comment

by:José Perez
ID: 41836237
I have created tblCustomerHistory with 3 columns:
  • ("creation_date" for the 'right_part' as you called it.
  • "id_empresa" for 'id_Empresa' (company id)
  • "log" for the 'left part' in your example

I also modified your query to:
INSERT INTO tblCustomerHistory (creation_date, id_empresa, log)
SELECT 
            case when ca2.pos1 > 0 then substring(SplitString .value, ca2.pos1 + 7, len(SplitString.value)) end right_part
     ,  c.id_Empresa
     , case when ca2.pos1 > 0 then left(SplitString.value, ca2.pos1 + 6) end left_part

FROM tblCustomer c
CROSS APPLY dbo.SplitStringIntoTable ( replace(c.log,'<br>','~'), '~' ) as SplitString
CROSS APPLY (select charindex('Activo',SplitString.value) as pos1 ) as ca2

Open in new window


And after running it, it leaves several blank fields, probably because "Activo" is not the only criteria, it can be any of 3 texts values ("Activo", "Bloquedao" or "Inactivo") any idea how can I fix it?
Please see attached image.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41836422
I have NOT TESTED the following at all, and it  may not be a very sophisticated approach but one way is to use unions perhaps.

for example:
-- INSERT INTO tblCustomerHistory (creation_date, id_empresa, log)
        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 7, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 6)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Activo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Activo', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 10, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 9)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Bloquedao', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Bloquedao', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 9, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 8)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('inactivo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('inactivo', SplitString) > 0

Open in new window

Use a select query first to see if it produces valid data

Please note: I do not have test data to work from
0
 
LVL 2

Author Comment

by:José Perez
ID: 41837745
Msg 102, Level 15, State 1, Line 51
Incorrect syntax near 'all'.

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41837754
The great problem for anyone trying to answer just an error message is we don't actually know what it is that you actually you executed. e.g. "Line 51" suggests you added 2 lines to my 49 line suggestion.

Please also keep in mind that I really cannot test anything I am proposing because I do not have test data.

However, despite those comments, I think the error is my fault (sorry), and I suspect the union should have been performed via a CTE (common table expression) like this:
with CTE as (
        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 7, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 6)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Activo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Activo', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 10, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 9)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Bloquedao', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Bloquedao', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 9, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 8)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('inactivo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('inactivo', SplitString) > 0
    )
--INSERT INTO tblCustomerHistory (creation_date, id_empresa, log)
    SELECT creation_date, id_empresa, [log]
    FROM CTE

Open in new window


At this point I have to encourage you to investigate error messages before you add them to comments here. Look at the listed line number of the code you used that produced an error, works upwards from that point ou may spot the problem yourself.

If you do get an error you cannot solve, please using a code block and include the actual code used, along with the error message.
0
 
LVL 2

Author Comment

by:José Perez
ID: 41837762
Initially, I just added 2 'Enter'
I checked the issue but as I understand it, it recognizes 'SplitString' as a column.

Now it displays the following error:
Msg 207, Level 16, State 1, Line 15
Invalid column name 'SplitString'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'SplitString'.
Msg 207, Level 16, State 1, Line 49
Invalid column name 'SplitString'.

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41837932
LInes 15, 32 and 49 all refer to where clauses

so there is a problem in those lines, e.g. line 15 is

        WHERE CHARINDEX('Activo', SplitString) > 0

The reported error is Invalid column name 'SplitString'.

so what is different about the reference to SplitString in line 15 for previous references to SplitString ........... in previous references it was SplitString.value

so, line 15 really should have been:

        WHERE CHARINDEX('Activo', SplitString.value) > 0

and make equivalent changes in the other lines

==========
I encourage you to learn how to read the error message and troubleshoot this reported error, ultiimately gaining that skill will save you a lot of time.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:José Perez
ID: 41839389
I have researched but the issue is in the query, it is not recognizing all the columns.
Please see attached image.Captura.PNG
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41839622
Did you try to correct it? The CTE has 3 columns but uses different names.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41839842
I CANNOT guarantee that the code I provide here will operate without error..... because I have NO DATA to test with

That means delays if we treat every error like a warranty claim.

To assist in solving the error message, run this
with CTE as (
        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 7, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 6)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Activo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Activo', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 10, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 9)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Bloquedao', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Bloquedao', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 9, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 8)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('inactivo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('inactivo', SplitString) > 0
    )
select
*
from CTE

Open in new window

Is that data correct?
What are the column names?
for look at this:
    SELECT creation_date, id_empresa, [log]
    FROM CTE

are they the same column names?
do those columns "line up"? (i.e. a date under a date related heading?

by the way:
would a string of text magically become a real date?

These and other mysteries could be solved by providing me with sample data

right_part, id_Empresa, left_part
creation_date, id_empresa, [log]

see if this works without error:
with CTE as (
        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 7, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 6)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Activo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Activo', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 10, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 9)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('Bloquedao', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('Bloquedao', SplitString) > 0

        UNION ALL

        SELECT
                CASE
                        WHEN ca2.pos1 > 0 THEN SUBSTRING(SplitString.value, ca2.pos1 + 9, LEN(SplitString.value))
                END right_part
              , c.id_Empresa
              , CASE
                        WHEN ca2.pos1 > 0 THEN LEFT(SplitString.value, ca2.pos1 + 8)
                END left_part

        FROM tblCustomer c
        CROSS APPLY dbo.SplitStringIntoTable(REPLACE(c.log, '<br>', '~'), '~') AS SplitString
        CROSS APPLY (SELECT
                        CHARINDEX('inactivo', SplitString.value) AS pos1) AS ca2
        WHERE CHARINDEX('inactivo', SplitString) > 0
    )
--INSERT INTO tblCustomerHistory (creation_date, id_empresa, log)
    SELECT cast(right_part as date) as creation_date, id_Empresa, left_part as [log]
    FROM CTE

Open in new window

NB, this is new to that query: cast(right_part as date) as creation_date
0
 
LVL 2

Author Comment

by:José Perez
ID: 41845165
Error again. Attached is a backup of database for you to test, sorry not to uploaded before.

Msg 207, Level 16, State 1, Line 15
Invalid column name 'SplitString'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'SplitString'.
Msg 207, Level 16, State 1, Line 49
Invalid column name 'SplitString'.

Open in new window


Please rename file "erp_database_bak.jpg" to "erp_database.bak" so you can import it to mssql.
erp_database_bak.zip
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41848093
Have you created the needed function? (to split the long string into rows)
Have you verified the function works? (how?)
What is the name of that function?

Please answer those 3  questions, AND run this query:
declare @string varchar(8000)
declare @delimiter varchar(5) = '~'

set @string = '<br>Cambio estado (1) inactivo 7/28/2016 12:00:00 AM<br>Cambio estado (1) Activo 7/28/2016 12:00:00 AM<br>Cambio estado (1) Bloquedao 7/28/2016 12:00:00 AM<br>Cambio estado (1) Activo 7/28/2016 12:00:00 AM<br>Cambio estado (1) Bloquedao 7/28/2016 12:00:00 AM'

set @string = replace(@string,'<br>','~')

--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
;WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
), 
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
select
      value_seq
    , case when len(value)>0 then left(value,len(value)-22) end log_string
    , case when len(value)>0 then try_cast(right(value,22) as date) end log_date
from (
SELECT
      ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq
    , LTRIM(RTRIM(SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)))) AS value
FROM cteStart s
) x
where len(value) > 22

Open in new window

What is the result of that query? (please copy/paste the result)

If that query produces an error which mentions "try_cast" :
    Change the "try_cast" to "cast" in my query (line 33) and re-run.
    What is the result of that re-run?
0
 
LVL 2

Author Comment

by:José Perez
ID: 41854749
Answers:
1. Have you created the needed function? (to split the long string into rows)
A. No. I just used run the queries and reported results to you since I am a developer not a DB guy.

2. Have you verified the function works? (how?)
A. Same answer as number 1.

What is the name of that function?
A. What function?

Result of the query (looks good):

value_seq      log_string                           log_date
2      Cambio estado (1) inactivo                   2016-07-28
3      Cambio estado (1) Activo                 2016-07-28
4      Cambio estado (1) Bloquedao        2016-07-28
5      Cambio estado (1) Activo                 2016-07-28
6      Cambio estado (1) Bloquedao           2016-07-28
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41854807
OK. Thanks for getting back to me.

You NEED a FUNCTION

That function "splits" your long string into pieces.

After you have the function is created then we can solve the problem.

This code creates the function:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
RETURNS TABLE WITH SCHEMABINDING 
AS
--SELECT * FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values CROSS APPLY DBA.dbo.SplitStringIntoTable(test_values.string, '/')
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
), 
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq,
       LTRIM(RTRIM(SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)))) AS value
FROM cteStart s;

GO

Open in new window


TEST that this function work by running this query:
SELECT * 
FROM (
       SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string
      ) AS test_values 
CROSS APPLY dbo.SplitStringIntoTable(test_values.string, '/')

Open in new window


Please let me know if you can get the function created and that the query immediately above this sentence works ok.
0
 
LVL 2

Author Comment

by:José Perez
ID: 41854819
The first query works and create the function without any issues, "Succesfully"
The second displays the attached image result.Results
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 41854829
Perfecto!

Now try this query please:
SELECT 
       c.id_Empresa
     , case when len(value)>0 then left(value,len(value)-22) end log_string
     , case when len(value)>0 then try_cast(right(value,22) as date) end log_date
FROM tblCustomer c
CROSS APPLY dbo.SplitStringIntoTable ( replace(c.log,'<br>','~'), '~' ) as SplitString
WHERE LEN(value) > 22
;

Open in new window

0
 
LVL 2

Author Comment

by:José Perez
ID: 41854834
Result is attached into image.Query result
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 41854843
muy bien!

So, now you have a query that you can use to INSERT INTO another table.

You can add something like this as the first line:

      INSERT INTO tblCustomerHistory (id_Empresa, log_string, log_date)

BUT the columns in bold must exist in that table. Basically I have shown you how to divide your long strings into many rows, and to divide the string so you get a real date as well.

It is now YOUR JOB to align the working query to columns of a table where you will store the data.

ok?
0
 
LVL 2

Author Comment

by:José Perez
ID: 41855262
ok. Thanks a lot!
0
 
LVL 2

Author Closing Comment

by:José Perez
ID: 41855263
Excelent solution!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41855647
Thank you.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now