Post reply

Walking through Data

  • March 10, 2006 at 10:40 am

    #93662

    As part of a script I have the following code. The idea is to retrieve specific data from emails whose body has been imported into a SQL Server Table name "tblEmailTest".

    This part works fine if I only have one record but If the table contains more I get the error message that the @bod variable can't contain multiple values, which makes perfect sense.

    My question is how can I walk through the data row by row and retrieve the data for each one?  

    Declare @Bod as varchar (1000)  -- The actual Body of the Email

      Select @Bod = (Select Body from tblEmailTest)

      Declare @SNStart as int   -- The number where the SN Starts 

      Declare @SNLength as int  --The lenght of the SN to be used in the substring function

      Select @SNStart = (Select (CHARINdex('Serial Number:', @Bod))+ 15)

      Select @SNLength = (Select CharIndex('Current Meter:', @Bod) - @SNStart)

      Select substring(@Bod, @SNStart, @SNLength)

  • March 10, 2006 at 11:39 am

    #626054

    In case you're always looking for the same predefined thing there's no need to loop:

    SELECT

     Substring(E.BODY, CHARINdEX('Serial Number:',E.BODY), CHARINDEX('Current Meter:', E.BODY)-CHARINdEX('Serial Number:',E.BODY)) AS yourString,

     'thank you paramind' AS myString

    FROM tblEmailTest AS E


    _/_/_/ paramind _/_/_/

  • March 10, 2006 at 12:24 pm

    #626065

    This was exactly what I needed. Thanks much Paramind.

  • March 10, 2006 at 12:30 pm

    #626069

    You're welcome. My PayPal account: paramind@ars-data.de. I think, 10 cents will do


    _/_/_/ paramind _/_/_/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply