title: sql scalar-valued functions incredibly slow
body: <p>SQL Server 2012 - I have a view (complicated) and one of the columns needs to have anything non-numeric stripped out.  The following works to a point;</p>
  
  <pre><code>STUFF(dbo.campaign_tracking_clicks.tt_cpn, 1, PATINDEX('%[0-9]%', dbo.campaign_tracking_clicks.tt_cpn) - 1, '') AS emailerid
  </code></pre>
  
  <p>I get an error if anything but numbers are at the end of the value.</p>
  
  <p>I have a scalar-valued function'</p>
  
  <pre><code>/****** Object:  UserDefinedFunction [dbo].[KeepNumCharacters]    Script Date: 10/11/2016 1:05:51 PM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER Function [dbo].[KeepNumCharacters](@Temp VarChar(100)) Returns VarChar(100)
   AS
   Begin    
   While PatIndex('%[^0-9]%', @Temp) &gt; 0  
   Set @Temp = Stuff(@Temp, PatIndex('%[^0-9+]%', @Temp), 1, '')    
    Return @TEmp
   End
  </code></pre>
  
  <p>I'm using;</p>
  
  <pre><code>dbo.KeepNumCharacters(dbo.campaign_tracking_clicks.tt_cpn) AS emailerid
  </code></pre>
  
  <p>But, it's taking a very long time to execute.  I've searched and searched but without finding an alternative.</p>