TSQL Message Filter

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  August 22, 2008 at 4:35 am

  #71781

  Hi All,

  I am trying to filter the below flat file message which is stored in a sql table. But i am not able to fileter successfully. Here the table structure and data.

  create table #t1

  (

  Message varchar(8000)

  )

  insert into #t1

  select '

  {1:F01PARBESMXFXXX0074301877}

  {2:I950MLILGB4LXKSIN}

  {3:{108:MMD0192030369000}}

  {4:

  :20:GL0710250013

  :25:0000110405

  :28C:00211/00001

  :60F:C071024EUR778881,80

  :61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148

  ES0111845014 AC.ABERTIS INFRAE

  :61:0710251025CR1,31NDIVCD879992ES01//1CD879992

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880006ES01//1CD880006

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880008ES01//1CD880008

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880025ES01//1CD880025

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880029ES01//1CD880029

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880037ES01//1CD880037

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880184ES01//1CD880184

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,64NDIVCD879994ES01//1CD879994

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880020ES01//1CD880020

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880027ES01//1CD880027

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880042ES01//1CD880042

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,28NDIVCD880186ES01//1CD880186

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880021ES01//1CD880021

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880023ES01//1CD880023

  ES0109427734 AC. ANTENA 3

  :62M080721EUR348502750,89

  }

  {5:{CHK:E3F9937E8D52}}

  {1:F21PARBESMXFXXX0074301877}

  {4:{177:0807212311}

  {451:0}

  {108:MMD0192030369000}}'

  My Requirement is :

  I want to filter or retrieve the string parts which start with ':61:'.

  Expected output:

  -------------------------------------------------------------

  :61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148

  :61:0710251025CR1,31NDIVCD879992ES01//1CD879992

  :61:0710251025CR1,31NDIVCD880006ES01//1CD880006

  :61:0710251025CR1,31NDIVCD880008ES01//1CD880008

  :61:0710251025CR1,31NDIVCD880025ES01//1CD880025

  :61:0710251025CR1,31NDIVCD880029ES01//1CD880029

  :61:0710251025CR1,31NDIVCD880037ES01//1CD880037

  :61:0710251025CR1,31NDIVCD880184ES01//1CD880184

  :61:0710251025CR1,64NDIVCD879994ES01//1CD879994

  :61:0710251025CR2,62NDIVCD880020ES01//1CD880020

  :61:0710251025CR2,62NDIVCD880027ES01//1CD880027

  :61:0710251025CR2,62NDIVCD880042ES01//1CD880042

  :61:0710251025CR3,28NDIVCD880186ES01//1CD880186

  :61:0710251025CR3,94NDIVCD880021ES01//1CD880021

  :61:0710251025CR3,94NDIVCD880023ES01//1CD880023

  ----------------------------------------------------------------

  Inputs are highly appreciable !

  karthik

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 22, 2008 at 5:44 am

  #861807

  Using a few changes to the ever-popular uftSplitString function,

  [font="Courier New"]

  CREATE TABLE #t1

  (

  MESSAGE VARCHAR(8000)

  )

  INSERT INTO #t1

  SELECT '

  {1:F01PARBESMXFXXX0074301877}

  {2:I950MLILGB4LXKSIN}

  {3:{108:MMD0192030369000}}

  {4:

  :20:GL0710250013

  :25:0000110405

  :28C:00211/00001

  :60F:C071024EUR778881,80

  :61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148

  ES0111845014 AC.ABERTIS INFRAE

  :61:0710251025CR1,31NDIVCD879992ES01//1CD879992

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880006ES01//1CD880006

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880008ES01//1CD880008

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880025ES01//1CD880025

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880029ES01//1CD880029

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880037ES01//1CD880037

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880184ES01//1CD880184

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,64NDIVCD879994ES01//1CD879994

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880020ES01//1CD880020

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880027ES01//1CD880027

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880042ES01//1CD880042

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,28NDIVCD880186ES01//1CD880186

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880021ES01//1CD880021

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880023ES01//1CD880023

  ES0109427734 AC. ANTENA 3

  :62M080721EUR348502750,89

  }

  {5:{CHK:E3F9937E8D52}}

  {1:F21PARBESMXFXXX0074301877}

  {4:{177:0807212311}

  {451:0}

  {108:MMD0192030369000}}'

  --------------

  DECLARE @String VARCHAR(8000), @Delimiter VARCHAR(4)

  SELECT @String = [Message] FROM #t1

  SET @Delimiter = ':61:'

  SELECT * FROM (

     SELECT SUBSTRING(@String+@Delimiter, number-LEN(@Delimiter),

             CHARINDEX(@Delimiter, @String+@Delimiter, number) - number) AS MESSAGE

     FROM Numbers

     WHERE number <= LEN(REPLACE(@String,' ','|'))

     AND SUBSTRING(@Delimiter + @String, number, LEN(REPLACE(@Delimiter,' ','|'))) = @Delimiter

  ) d

  WHERE MESSAGE LIKE @Delimiter + '%'

  [/font]

  The outer or Onion (TM JBModen) select gets rid of the first row.

  ^{“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

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  August 22, 2008 at 5:46 am

  #861810

  karthikeyan (8/22/2008)

  ---

  Hi All,

  I am trying to filter the below flat file message which is stored in a sql table. But i am not able to fileter successfully. Here the table structure and data.

  create table #t1

  (

  Message varchar(8000)

  )

  insert into #t1

  select '

  {1:F01PARBESMXFXXX0074301877}

  {2:I950MLILGB4LXKSIN}

  {3:{108:MMD0192030369000}}

  {4:

  :20:GL0710250013

  :25:0000110405

  :28C:00211/00001

  :60F:C071024EUR778881,80

  :61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148

  ES0111845014 AC.ABERTIS INFRAE

  :61:0710251025CR1,31NDIVCD879992ES01//1CD879992

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880006ES01//1CD880006

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880008ES01//1CD880008

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880025ES01//1CD880025

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880029ES01//1CD880029

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880037ES01//1CD880037

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880184ES01//1CD880184

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,64NDIVCD879994ES01//1CD879994

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880020ES01//1CD880020

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880027ES01//1CD880027

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880042ES01//1CD880042

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,28NDIVCD880186ES01//1CD880186

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880021ES01//1CD880021

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880023ES01//1CD880023

  ES0109427734 AC. ANTENA 3

  :62M080721EUR348502750,89

  }

  {5:{CHK:E3F9937E8D52}}

  {1:F21PARBESMXFXXX0074301877}

  {4:{177:0807212311}

  {451:0}

  {108:MMD0192030369000}}'

  My Requirement is :

  I want to filter or retrieve the string parts which start with ':61:'.

  Expected output:

  -------------------------------------------------------------

  :61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148

  :61:0710251025CR1,31NDIVCD879992ES01//1CD879992

  :61:0710251025CR1,31NDIVCD880006ES01//1CD880006

  :61:0710251025CR1,31NDIVCD880008ES01//1CD880008

  :61:0710251025CR1,31NDIVCD880025ES01//1CD880025

  :61:0710251025CR1,31NDIVCD880029ES01//1CD880029

  :61:0710251025CR1,31NDIVCD880037ES01//1CD880037

  :61:0710251025CR1,31NDIVCD880184ES01//1CD880184

  :61:0710251025CR1,64NDIVCD879994ES01//1CD879994

  :61:0710251025CR2,62NDIVCD880020ES01//1CD880020

  :61:0710251025CR2,62NDIVCD880027ES01//1CD880027

  :61:0710251025CR2,62NDIVCD880042ES01//1CD880042

  :61:0710251025CR3,28NDIVCD880186ES01//1CD880186

  :61:0710251025CR3,94NDIVCD880021ES01//1CD880021

  :61:0710251025CR3,94NDIVCD880023ES01//1CD880023

  ----------------------------------------------------------------

  Inputs are highly appreciable !

  I hope it helps 😀

  declare @STR varchar(8000)

  declare @str1 varchar(8000)

  declare @str2 table(msg varchar(8000))

  select @STR = ms from #t1

  set @str1 = ''

  declare @i int

  declare @y int

  while(charindex(':61:', @STR) <> 0)

  begin

  select @i = charindex(':61:',@str)

  select @y = charindex(char(13),substring(@str, @i, len(@str)-@i))

  print @i

  print @y

  select @str1 = substring(@str,@i, @y)

  insert into @str2 select @str1

  set @STR = substring(@str, @i+2, len(@str))

  end

  select * from @str2

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  August 22, 2008 at 5:55 am

  #861820

  Here's my effort. I was assuming there could be multiple messages in #t1, which makes it a bit trickier. I'm not proud of it, but anyway...

  --Sample Data

  --drop table #t1

  create table #t1

  (

  Message varchar(8000)

  )

  insert into #t1

  select '

  {1:F01PARBESMXFXXX0074301877}

  {2:I950MLILGB4LXKSIN}

  {3:{108:MMD0192030369000}}

  {4:

  :20:GL0710250013

  :25:0000110405

  :28C:00211/00001

  :60F:C071024EUR778881,80

  :61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148

  ES0111845014 AC.ABERTIS INFRAE

  :61:0710251025CR1,31NDIVCD879992ES01//1CD879992

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880006ES01//1CD880006

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880008ES01//1CD880008

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880025ES01//1CD880025

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880029ES01//1CD880029

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880037ES01//1CD880037

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,31NDIVCD880184ES01//1CD880184

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR1,64NDIVCD879994ES01//1CD879994

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880020ES01//1CD880020

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880027ES01//1CD880027

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR2,62NDIVCD880042ES01//1CD880042

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,28NDIVCD880186ES01//1CD880186

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880021ES01//1CD880021

  ES0109427734 AC. ANTENA 3

  :61:0710251025CR3,94NDIVCD880023ES01//1CD880023

  ES0109427734 AC. ANTENA 3

  :62M080721EUR348502750,89

  }

  {5:{CHK:E3F9937E8D52}}

  {1:F21PARBESMXFXXX0074301877}

  {4:{177:0807212311}

  {451:0}

  {108:MMD0192030369000}}'

  union all select ':61:fewefe

  dfskdsfklj'

  union all select ':52:dsfjksdf

  :61:dfskdsfklj'

  union all select '72dsfjksdf

  :61:fdkjsfljdfsj'

  union all select ''

  union all select null

  --Query

  declare @t table (Message varchar(8000))

  insert @t select replace(Message, ':61:', '¬') from #t1

  while @@rowcount > 0

  update b set Message = stuff(Message, UnwantedRowPosition, UnwantedRowLength+1, '') from (

  select *, charindex(char(10), substring(Message+char(10), UnwantedRowPosition+1, 8000)) as UnwantedRowLength

  from (

  select Message, patindex('%' + char(10) + '[^¬]%', char(10) + Message) as UnwantedRowPosition from @t) a

  where UnwantedRowPosition > 0) b

  select replace(Message, '¬', ':61:') from @t

  Note: Assumes '¬' is not contained in Message - if it is replace it with a character which isn't in there, if possible.

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 22, 2008 at 7:34 am

  #861882

  Good point Ryan:

  [font="Courier New"]DECLARE @Delimiter VARCHAR(4)

  SET @Delimiter = ':61:'

  SELECT * FROM (

     SELECT SUBSTRING([Message]+@Delimiter, number-LEN(@Delimiter),

             LEN(@Delimiter)+CHARINDEX(@Delimiter, [Message]+@Delimiter, number) - number) AS MESSAGE

     FROM Numbers, #t1

     WHERE number <= LEN(REPLACE([Message],' ','|'))

     AND SUBSTRING(@Delimiter + [Message], number, LEN(REPLACE(@Delimiter,' ','|'))) = @Delimiter

  ) d

  WHERE MESSAGE LIKE @Delimiter + '%'[/font]

  ^{“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

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  August 27, 2008 at 1:50 am

  #863548

  Sorry a little late here.

  Thanks a lot Ryan,Chris and Arjun ! I really appreciate your's help !

  karthik