title: call function in Update statement Sybase 15x
body: <p>I have a situation where I'm parsing a varchar value to extract numbers, and I have to update my table to put the numeric value into a new field in my table.  I have encapsulated the string parsing into a stored proc.  Now I'm trying to write the update statement.</p>
  
  <p>MY table looks like this (simplified to show only the key values):</p>
  
  <pre><code>CREATE TABLE dbo.oper_pacer
  (
  oper_pacer_id                 int         NOT NULL,
  oper_pacer_fluroscopy         varchar(20) NULL,
  oper_pacer_fluoro_min         float       NULL
  )
  </code></pre>
  
  <p>My function takes a varchar(20) value and returns a float, doing the parsing and conversion of the number:</p>
  
  <pre><code>CREATE FUNCTION usf_ParseFluoro( 
  @parm1 VARCHAR(20)
  )
  RETURNS FLOAT
  AS
  BEGIN
      DECLARE @value VARCHAR(20)
      DECLARE @midVal VARCHAR(20)
      DECLARE @nPos INT
      DECLARE @lPos INT
  
  SELECT @midVal = SUBSTRING(@parm1, PATINDEX('%[0-9]%', @parm1), PATINDEX('%[0-9]%',@parm1) + LEN(@parm1)+1)
  
  SELECT @value = 
      (CASE 
          WHEN PATINDEX('%[a-z]%', @midVal) = 0
              THEN @midVal
          ELSE RTRIM(SUBSTRING(@midVal, 1, PATINDEX('%[a-z]%', @midVal)-1))
      END)
  
      RETURN CONVERT(FLOAT, @value)
  END
  </code></pre>
  
  <p>Here's my problem:  </p>
  
  <p>How do I write the update of my original table, to loop through, calling the function, and putting the return value into oper_pacer_fluoro_min, given that there's no way to do a for each loop in T-SQL?</p>
  
  <p><strong>EDIT:</strong>
  Once I created the function, the Update was pretty simple, with a little trial and error:  I needed to put in the dbo. prefix to the function to get it to run with my current DB permissions:</p>
  
  <pre><code>UPDATE oper_pacer SET oper_pacer_fluoro_min = dbo.usf_ParseFluoro(oper_pacer_fluroscopy)
  </code></pre>