Creating and calling a function

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  April 26, 2018 at 8:19 am

  #328594

  I am trying to simplified a routine process I write into queries using a user defined function instead of this verbose code that strips all non-numeric characters from a phone number:

  SELECT T.peopleId, T.Name,
replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(T.phoneNumber,'(',''),')',''),'-',''),' ',''),',',''),
PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(T.phoneNumber,'(',''),')',''),'-',''),' ',''),',','')), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(replace(replace(replace(replace(replace(T.phoneNumber,'(',''),')',''),'-',''),' ',''),',',''),
PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(T.phoneNumber,'(',''),')',''),'-',''),' ',''),',','')), 8000) + 'X') -1),'.','') AS 'StrippedPhone',
FROM TMHP_DB..People

  I found a function example but not sure how to call it in the above or if I have it worded correctly:
  USE [TMHP_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[fnRemoveNonNumChars](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%', @strText) > 0
  BEGIN
   SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
  END
  RETURN @strText
END
GO
  

• Thom A

  SSC Guru

  Points: 99130

  More actions

  April 26, 2018 at 8:38 am

  #1988362

  Personally, I would use a different function, a WHILE loop isn't going to perform well and nor is a scalar function. This uses Alan Burstein's NGrams8K: http://www.sqlservercentral.com/articles/Tally+Table/142316/.

  After you have that function, you can create the function to remove the non-numeric values:
  CREATE FUNCTION dbo.RemoveNonNumeric (@Phone varchar(8000)) RETURNS TABLE
AS RETURN

    SELECT (SELECT N.token + ''
    FROM dbo.NGrams8k(@Phone,1) N
    WHERE N.token LIKE '[0-9]'
    ORDER BY N.position
    FOR XML PATH('')) AS PhoneNum
GO


  Now you can call it easily using APPLY. For example:
  WITH VTE AS (
  SELECT *
  FROM (VALUES ('123-456,789'),
      ('1245,6957,4'),
      ('12346 647247')) V(Phone))
SELECT *
FROM VTE V
  CROSS APPLY RemoveNonNumeric(V.Phone) RNN;
GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  April 26, 2018 at 10:48 am

  #1988388

  This worked and thanks to the link to the Alan Burstein article!