I created a customisation recently for a client which would generate a Vendor ID based on the name, by removing alphanumeric characters. In order to make it as flexible as possible, I created the function to accept a parameter for type which will cause the function to strip different characters:
- A – leaves alpha characters only.
- N – leaves numeric characters only.
- AN – leaves alphanumeric characters.
The second parameter is the string which should have the characters stripped:
IF OBJECT_ID (N'uv_AZRCRV_StripCharacters', N'FN') IS NOT NULL DROP FUNCTION uv_AZRCRV_StripCharacters GO CREATE FUNCTION uv_AZRCRV_StripCharacters(@Type VARCHAR(100), @String VARCHAR(MAX)) RETURNS VARCHAR(MAX) /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ BEGIN DECLARE @PatIndex VARCHAR(20) IF (@Type = 'Alpha' OR @Type = 'A') SET @PatIndex = '%[^a-z]%' IF (@Type = 'Numeric' OR @Type = 'N') SET @PatIndex = '%[^0-9]%' IF (@Type = 'AlphaNumeric' OR @Type = 'AN') SET @PatIndex = '%[^a-z0-9]%' WHILE PATINDEX(@PatIndex, @String) < 0 SET @String = STUFF(@String, PATINDEX(@PatIndex, @String), 1, '') RETURN @String END GRANT EXECUTE ON uv_AZRCRV_StripCharacters TO DYNGRP GO