Loading ...
Sorry, an error occurred while loading the content.
 

Update Query

Expand Messages
  • Alex Benitez
    I want to do an update but I m having a brain lapse...hopefully someone can help me. This is my data: claim key (Field that has data) AUWAK_WHS2001000234
    Message 1 of 4 , May 7 11:59 AM
      I want to do an update but I'm having a brain lapse...hopefully someone can
      help me.

      This is my data:

      claim key (Field that has data)
      AUWAK_WHS2001000234
      AUWAKDSD2002011983
      AUANP_WHS2001000234
      AUANPDSD2002485480292

      basically .. After the last letter is where the year starts( 4 digit) and
      then after that is where the claim number starts. The claim number can vary
      in length... How do I grab just the claim number to update a field (DM#)?

      Update mytable
      set DM# =

      Thanks,
      Alex
    • Necdet Çetinkaya
      Hý I write something if you create a function like this you can get thep parts of data for example This is data AUWAK_WHS200100023 This is seperated data
      Message 2 of 4 , May 8 12:23 AM

        Hı I write something if you create a function like this you can get thep parts of data for example

        This is data

        AUWAK_WHS200100023

        This is seperated data

        FIRSTPART  YEAR    CLAIM          

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

        AUWAK_WHS  2001 00023

         

         

        DECLARE @A VARCHAR(100),

                    @i INT,

                    @LEN INT ,

                    @YEAR VARCHAR(4),

                    @FIRSTPART    VARCHAR(10),

                    @YEARCLAIM    VARCHAR(15),

                    @CLAIM                       VARCHAR(15)

        SET @YEAR = ''

        SET @FIRSTPART = ''

        SET @YEARCLAIM = ''

        SET @i=1

         

        SET @a = 'AUWAK_WHS2001000234'

        SELECT @LEN = LEN(@a)

        WHILE  @i< @LEN

        BEGIN

         

                     IF (SELECT       ISNUMERIC(SUBSTRING(@a,@i,1)) )= 0

                    SET @FIRSTPART = @FIRSTPART + SUBSTRING(@a,@i,1)

                    ELSE 

                    BEGIN

                                SET @YEARCLAIM = @YEARCLAIM + SUBSTRING(@a,@i,1)

         

                    END

         

                   

        SET @i=@i+1

        END

        SELECT @YEAR= SUBSTRING(@YEARCLAIM,1,4),@CLAIM=  SUBSTRING(@YEARCLAIM,5,LEN(@YEARCLAIM) -1)

         

        SELECT

                    'FIRSTPART' = @FIRSTPART,

                    'YEAR'=@YEAR ,

                    'CLAIM' = @CLAIM

         

        Necdet Çetinkaya

        Genotıp Hasta Bilgi Sistemleri

        Genotıp Patient Information Systems

        necdet@...

         

        -----Original Message-----
        From: Alex Benitez [mailto:alex.benitez@...]
        Sent:
        Friday, May 07, 2004 9:59 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Update Query

         

        I want to do an update but I'm having a brain lapse...hopefully someone can
        help me.

        This is my data:

        claim key (Field that has data)
        AUWAK_WHS2001000234
        AUWAKDSD2002011983
        AUANP_WHS2001000234
        AUANPDSD2002485480292

        basically .. After the last letter is where the year starts( 4 digit) and
        then after that is where the claim number starts.  The claim number can vary
        in length... How do I grab just the claim number to update a field (DM#)?

        Update mytable
        set DM# =

        Thanks,
        Alex




      • Razvan Socol
        ... SUBSTRING(ClaimKey, PATINDEX( %[0-9]% ,ClaimKey)+4,100) Razvan
        Message 3 of 4 , May 8 6:19 AM
          Alex Benitez wrote:
          > [...] After the last letter is where the year starts( 4 digit) and
          > then after that is where the claim number starts. The claim number can
          > vary
          > in length... How do I grab just the claim number ?

          SUBSTRING(ClaimKey, PATINDEX('%[0-9]%',ClaimKey)+4,100)

          Razvan
        • Alex Benitez
          Thanks all for your help and great solutions... I ended up using the Patindex() function which worked like a charm. Necdet, thanks for the function... I will
          Message 4 of 4 , May 10 6:12 AM
            Thanks all for your help and great solutions... I ended up using the
            Patindex() function which worked like a charm. Necdet, thanks for the
            function... I will use that for another similar issue.

            alex

            -----Original Message-----
            From: Razvan Socol [mailto:rsocol@...]
            Sent: Saturday, May 08, 2004 9:20 AM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: Re: [SQLQueriesNoCode] Update Query


            Alex Benitez wrote:
            > [...] After the last letter is where the year starts( 4 digit) and
            > then after that is where the claim number starts. The claim number can
            > vary
            > in length... How do I grab just the claim number ?

            SUBSTRING(ClaimKey, PATINDEX('%[0-9]%',ClaimKey)+4,100)

            Razvan






            Yahoo! Groups Links
          Your message has been successfully submitted and would be delivered to recipients shortly.