Parsing help

Transact-SQL
1

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.

2

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.

3

Hi Jeff,

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

4

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.

1 Like