Select specific set of data from text column

• SQListic

  SSCertifiable

  Points: 6904

  More actions

  August 19, 2013 at 10:45 pm

  #297124

  Hi Experts,

  How can i select the data from a table where the data is as follows.

  Requirement: Select rows from the table where the log entry is as "Number of Files count= 'is greater than zero"

  The query should select the related rows of that whole process.Following sample data has two download events, one with 4 files and other with 2 files.

  The query output should select where the process files is greater than zero. ignore when no files were processed

  DateTime TextColumn

  2011-03-09 14:40:35.413 Prcessed Files count=0

  2011-03-09 14:10:34.073 Prcessed Files count=2 --End 4

  2011-03-09 14:10:34.390 Success download for Z345330 --3

  2011-03-09 14:10:20.913 Success download for Z699090 --2

  2011-03-09 14:09:56.667 Number of Files count=2 --Start 1

  2011-03-09 13:39:55.280 Prcessed Files count=0

  2011-03-09 13:09:53.940 Prcessed Files count=0

  2011-03-09 12:39:52.770 Processed Files count=4 -- End 6

  2011-03-09 12:39:53.190 Success download for Z294754 -- 5

  2011-03-09 12:39:28.930 Success download for Z995876 --4

  2011-03-09 12:39:03.183 Success download for Z940565 -- 3

  2011-03-09 12:39:06.153 Success download for Z556433 -- 2

  2011-03-09 12:38:39.730 Number of Files count=4 ----Start 1

  Sample Output

  2011-03-09 12:38:39.730 Number of Files count=4 -

  2011-03-09 12:39:06.153 Success download for Z556433

  2011-03-09 12:39:03.183 Success download for Z940565

  2011-03-09 12:39:28.930 Success download for Z995876

  2011-03-09 12:39:53.190 Success download for Z294754

  2011-03-09 12:39:52.770 Processed Files count=4

  2011-03-09 14:09:56.667 Number of Files count=2

  2011-03-09 14:10:20.913 Success download for Z699090

  2011-03-09 14:10:34.390 Success download for Z345330

  2011-03-09 14:10:34.073 Prcessed Files count=2

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  August 19, 2013 at 11:24 pm

  #1642947

  can you please some more information what you required as its not clear to us...

  Also please post the DDL scripts to create the table and script to insert data into tables with some sample data and desired output...

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 20, 2013 at 1:17 am

  #1642959

  This looks straightforward. If you can set up the sample data as a CREATE TABLE/INSERT INTO script, I'm sure someone will help.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• HanShi

  SSC-Dedicated

  Points: 33510

  More actions

  August 20, 2013 at 2:06 am

  #1642968

  If I understand correctly, you want the complete results without the lines "Prcessed Files count=0".

  This query will give you these results:

  SELECT DateTime, TextColumn

  FROM Table_name

  WHERE TextColumn <> 'Prcessed Files count=0'

  ORDER BY DateTime

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• SQListic

  SSCertifiable

  Points: 6904

  More actions

  August 20, 2013 at 10:47 am

  #1643172

  Thanks Hanshi

  Your Post helped me