CHARINDEX/PATINDEX

I've used CHARINDEX for years, but came across a need to use a FILTER with a WILDCARD with CHARINDEX and found that it would not support it. 

Enter: PATINDEX. 

We'll use this article to put up tips on the uses of CHARINDEX and PATINDEX, for today this piece of code. The Task is to find 'INV12345678' in a notes field using wildcards and retrieve the invoice number for a report. 

Related Articles

... and you 'll find more on the SQL (General) Menu

SELECT Note_Display_String,
        CASE WHEN PATINDEX('%INV[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',Note_Display_String) > ''
            THEN SUBSTRING(
                    Note_Display_String,
                    PATINDEX('%INV[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',Note_Display_String)
                    ,11)
            ELSE '' end AS invoice,
        PATINDEX('%INV[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',Note_Display_String)
    FROM CN00100
    WHERE Note_Display_String LIKE '%inv%'
    ORDER BY 2 desc

 

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences