SQLTeam.com | Weblogs | Forums

Parsing help

Hi Guys,

I need help with the SQL query. I need help with parsing one field to 4 different fields. Here is my sample data

Blockquote
DECLARE @table_parsing TABLE (
id int IDENTITY(1,1),
Field1 varchar(4000)
);

INSERT INTO @table_parsing
(
--id - column value is auto-generated
Field1
)
SELECT 'test_stp_commer_ca_mm updated. 37345 records added. 0 records updated. 0 records deleted.'
UNION ALL
SELECT 'test_stp_commer_ca_da updated. 679 records added. 0 records updated. 0 records deleted.'
UNION ALL
SELECT 'test_stp_commer_ca updated. 768 records added. 0 records updated. 0 records deleted.'
UNION ALL
SELECT 'test_stp_commer_apps updated. 38263 records added. 0 records updated. 0 records deleted.'

SELECT * FROM @table_parsing tp

Blockquote

Here is the result that I want.

Parse1|Parse2 |Parse3 |Parse4

test_stp_commer_ca_mm updated| 37345 records added |0 records updated |0 records deleted.

test_stp_commer_ca_da updated.| 679 records added |0 records updated |0 records deleted.

test_stp_commer_ca updated. |768 records added |0 records updated |0 records deleted.

test_stp_commer_apps updated. |38263 records added |0 records updated |0 records deleted.

Any help would be much appreciated.

Tnx.

If the format of the data is consistent - that is, it always has 4 periods for the data you can do something like this:

Declare @table_parsing Table (id int Identity(1, 1), Field1 varchar(4000));

 Insert Into @table_parsing (Field1)
 Values ('test_stp_commer_ca_mm updated. 37345 records added. 0 records updated. 0 records deleted.')
      , ('test_stp_commer_ca_da updated. 679 records added. 0 records updated. 0 records deleted.')
      , ('test_stp_commer_ca updated. 768 records added. 0 records updated. 0 records deleted.')
      , ('test_stp_commer_apps updated. 38263 records added. 0 records updated. 0 records deleted.');

 Select *
      , Parse1 = substring(tp.Field1, 1, p1.pos - 1)
      , Parse2 = substring(tp.Field1, p1.pos + 1, p2.pos - p1.pos - 1)
      , Parse3 = substring(tp.Field1, p2.pos + 1, p3.pos - p2.pos - 1)
      , Parse4 = substring(tp.Field1, p3.pos + 1, p4.pos - p3.pos - 1)
   From @table_parsing tp
  Cross Apply (Values (charindex('.', tp.Field1, 1)))           As p1(pos)
  Cross Apply (Values (charindex('.', tp.Field1, p1.pos + 1)))  As p2(pos)
  Cross Apply (Values (charindex('.', tp.Field1, p2.pos + 1)))  As p3(pos)
  Cross Apply (Values (charindex('.', tp.Field1, p3.pos + 1)))  As p4(pos);

If there are missing segments then we would need to adjust the calculations for those entries.

Hi Jeff,

Thank you for your help. Let me run the query into actual data.
Once again, thank you for your effort and help.

Just for clarify - as I posted on the other forum:

Declare @table_parsing Table (id int Identity(1,1), Field1 varchar(4000));

 Insert Into @table_parsing (Field1)
 Values ('test_stp_commer_ca_mm updated. 37345 records added. 0 records updated. 0 records deleted.')
      , ('test_stp_commer_ca_da updated. 679 records added. 0 records updated. 0 records deleted.')
      , ('test_stp_commer_ca updated. 768 records added. 0 records updated. 0 records deleted.')
      , ('test_stp_commer_apps updated. 38263 records added. 0 records updated. 0 records deleted.')
      , ('test_stp_commer_apps updated. 38260 records added. 0 records updated.')
      , ('test_stp_commer_apps updated. 37260 records added.')
      , ('test_stp_commer_apps updated.')
      , ('test_stp_commer_apps updated with no periods included')
      , ('');


 Select *
      , Parse1 = substring(v.Field1, 1, p1.pos - 1)
      , Parse2 = substring(v.Field1, p1.pos + 1, p2.pos - p1.pos - 1)
      , Parse3 = substring(v.Field1, p2.pos + 1, p3.pos - p2.pos - 1)
      , Parse4 = substring(v.Field1, p3.pos + 1, p4.pos - p3.pos - 1)
   From @table_parsing                                          tp
  Cross Apply (Values (concat(tp.Field1, '....')))               v(Field1)
  Cross Apply (Values (charindex('.', v.Field1, 1)))            p1(pos)
  Cross Apply (Values (charindex('.', v.Field1, p1.pos + 1)))   p2(pos)
  Cross Apply (Values (charindex('.', v.Field1, p2.pos + 1)))   p3(pos)
  Cross Apply (Values (charindex('.', v.Field1, p3.pos + 1)))   p4(pos);

And if you want to include the periods - then we need to adjust the substring and add a coalesce & nullif but that can also be done.