PATINDEX pattern to replace character that is *not* first character-CodePudding

I'm trying to paritally 'leet-ify' a word, by taking a single character from a pre-defined set of replacable characters, and replacing it with a number/special character that resembles the letter being replaced. I don't want to replace the first character though.

Earlier I get a word that I've ensured has at least one letter after the first that is within the set of replaceable letters, and place it in the @Word variable. I then use the following to replace one letter. (Using 'loveable' as an example)

DECLARE @Word varchar(8) = 'lovable';
DECLARE @ReplaceChar varchar(1);


SET @ReplaceChar = SUBSTRING(@Word,PATINDEX('[a-z][abeilost]',@Word) 1,1);
SET @ReplaceChar = 
    (SELECT CASE @ReplaceChar
            WHEN 'a' THEN '@'
            WHEN 'b' THEN '8'
            WHEN 'e' THEN '3'
            WHEN 'i' THEN '!'
            WHEN 'l' THEN '1'
            WHEN 'o' THEN '0'
            WHEN 's' THEN '$'
            WHEN 't' THEN ' '
        END 
    );

SET @Word = STUFF(@Word,PATINDEX('[a-z][abeilost]',@Word) 1,1,@ReplaceChar);

As I understand it, PATINDEX should be finding the starting location of the first any-letter-followed-by-a-matching-letter string, and SUBSTRING/STUFF explicitly adds 1 to that number before collecting or replacing, so I should never get 'lovable' changed to '1oveable'... But that's what I'm getting. What am I missing?

CodePudding user response：

SELECT PATINDEX('[a-z][abeilost]', @Word)

Is returning 0. According to the documentation 0 means not found. The reason it is not being found it that your search criteria is for 2 characters only, the first being a single character which matches [a-z], and the second being a single character which matches [abeilost]. If you want to allow more characters you need to extend the search expression e.g.

SELECT PATINDEX('[a-z][abeilost]%', @Word)

Although I expect that isn't doing what you want as I imagine you want to repeat the specified characters any number of times which isn't possible with PATINDEX.

CodePudding user response：

After our talk in the comments above I'd suggest this approach:

DECLARE @Word varchar(8) = 'truth';

DECLARE @toBeReplaced VARCHAR(10)='abeilost';
DECLARE @replaceWith VARCHAR(10)='@83!10$ ';

DECLARE @position INT=PATINDEX(CONCAT('%[',@toBeReplaced,']%'),SUBSTRING(@word,2,8000)) 1;

SELECT STUFF(@word,@position,1,TRANSLATE(SUBSTRING(@word,@position,1),@toBeReplaced,@replaceWith));

The idea in short:

We define your translate parameters.
We find the position using PATINDEX() behind the first character.
Now we can use STUFF() to replace exactly one character at the given position by its translation.

For the next time: It would help a lot if you'd provided some samples with the expected result.

UPDATE

Using this at a tabular result, you can avoid the declared variable and do this inline:

DECLARE @WordTable TABLE(SomeText varchar(8));
INSERT INTO @WordTable VALUES('truth'),('loveable');

DECLARE @toBeReplaced VARCHAR(10)='abeilost';
DECLARE @replaceWith VARCHAR(10)='@83!10$ ';

--the new query

SELECT STUFF(wt.SomeText,pos,1,TRANSLATE(SUBSTRING(wt.SomeText,pos,1),@toBeReplaced,@replaceWith))
FROM @WordTable wt
CROSS APPLY(SELECT PATINDEX(CONCAT('%[',@toBeReplaced,']%'),SUBSTRING(wt.SomeText,2,8000)) 1) A(pos);