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 (https://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
This post is part of the series on using the Pi-hole; this series is a sub-series of the Adventures with a Raspberry Pi.