I'm trying to remove the letters from the beginning of the string only from the dbo.ProductCodes table.

I have:

I want:

If the letters were only at the beginning of the string, I could remove all letters using regex [^a-zA-z]. The problem is that letters appear not only at the beginning of the string.

EDIT: Also, I'd like to apply some exclusions to this logic. For instance, if the prefix is 'AA' or 'Q' or 'QA', I don't want to remove letters from the beginning of the string. Examples: Q12345, AA1234S, QA12345

CodePudding user response：

Updated for changed requirements:

SELECT ProductCode,
  Adjusted = SUBSTRING(ProductCode, 
    CASE WHEN ProductCode NOT LIKE 'Q%'
          AND ProductCode NOT LIKE 'QA%'
          AND ProductCode NOT LIKE 'AA%' THEN
    PATINDEX('%[0-9]%', ProductCode '0')
    ELSE 1 END, 255)
FROM dbo.ProductCodes;

• Example db<>fiddle also deals with no letters and all letters (empty result).

CodePudding user response：

Rather than SUBSTRING, I would personally use STUFF, as then you don't have to define a length of how many characters you want to retain, meaning that this will work for any length string. I also switch to looking for the first non-alpha character, rather than the first number, just to show the difference.

SELECT ProductCode,
       STUFF(ProductCode, 1, ISNULL(NULLIF(PATINDEX('%[^A-z]%', ProductCode),0)-1,0),'')
FROM dbo.ProductCodes;