CODEWITHSUNDEEP
sqlfunction
Iasha
Iasha

Reputation: 77

Using a SQL User Defined Function with an UPDATE

I have a Function that removes any non alpha numerical characters, and a stored procedure that does several updates to the EMIR table. My question is, how do I write an update statement to call the function RemoveNonAlphaNumericCharacters and update the field 'Underlying_Security_Name' 

USE EMIR
GO
ALTER FUNCTION [dbo].[RemoveNonAlphaNumericCharacters]
 (
-- Add the parameters for the function here
@String NVARCHAR(100)
 )
 RETURNS nvarchar(100)

AS
BEGIN
 DECLARE @Keep_Value AS NVARCHAR(50)
 SET @Keep_Value = '%[^a-z0-9 ]%'

 WHILE PATINDEX(@Keep_Value, @String) > 0
 BEGIN
  SET @String = STUFF(@String, PATINDEX(@Keep_Value, @String), 1, '')
 END

RETURN @String
 END

 GO

Upvotes: 1

Views: 2456

Answers (1)

Hatim Stovewala
Hatim Stovewala

Reputation: 1251

You could do something like this.

UPDATE EMIR.table_name 
SET Underlying_Security_Name = dbo.RemoveNonAlphaNumericCharacters(Underlying_Security_Name​);

Upvotes: 3

Related Questions