MS SQL: cut string at first numeric character

note: Search a field for the first numeric character or any other character(-class) and crop it

to find numbers in a string use:

PATINDEX('%[0-9]%',string)

if you want to get a substring from the first character to the first number use:

SUBSTRING( String ,1,PATINDEX('%[0-9]%', String )

If some of your strings do not contain any numbers you can use an inline condition:

CASE
    WHEN PATINDEX('%[0-9]%', string )>0 THEN
        SUBSTRING( string ,1,PATINDEX('%[0-9]%', string )-1)
    ELSE
        string
    END

This can be used in a query for example to count software installations without version numbers

SELECT  
    COUNT( DISTINCT id ) , 
    CASE
        WHEN PATINDEX('%[0-9]%', string )>0 THEN
            SUBSTRING( string ,1,PATINDEX('%[0-9]%', string )-1)
        ELSE
            string
        END
    AS Software
FROM table
GROUP BY 
    CASE
        WHEN PATINDEX('%[0-9]%', string )>0 THEN
            SUBSTRING( string ,1,PATINDEX('%[0-9]%', string )-1)
        ELSE
            string
        END

to find numbers in a string use:

PATINDEX('%[0-9]%',string)

if you want to get a substring from the first character to the first number use:

SUBSTRING( String ,1,PATINDEX('%[0-9]%', String )

If some of your strings do not contain any numbers you can use an inline condition:

CASE
    WHEN PATINDEX('%[0-9]%', string )>0 THEN
        SUBSTRING( string ,1,PATINDEX('%[0-9]%', string )-1)
    ELSE
        string
    END

This can be used in a query for example to count software installations without version numbers

SELECT  
    COUNT( DISTINCT id ) , 
    CASE
        WHEN PATINDEX('%[0-9]%', string )>0 THEN
            SUBSTRING( string ,1,PATINDEX('%[0-9]%', string )-1)
        ELSE
            string
        END
    AS Software
FROM table
GROUP BY 
    CASE
        WHEN PATINDEX('%[0-9]%', string )>0 THEN
            SUBSTRING( string ,1,PATINDEX('%[0-9]%', string )-1)
        ELSE
            string
        END

Leave a Reply