Split alphanumeric string into numeric and alpha parts in SQL Server

Lets say we have an alphanumeric string stored in a variable in SQL Server. The fist part of the string is numeric and the second part consists of letters and/or symbols specifying a unit or a percentage:

DECLARE @str VARCHAR(MAX) = '4000 ug/ML'

We now want to split the numeric / unit parts from the variable and return them as separate fields.

If the numeric part is always at the beginning, then we can use this expression:

PATINDEX('%[0-9][^0-9]%', @str)

to get the index of the last digit. To see how this works let us have a look at the manual for PATINDEX:

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

The pattern specified catches the first occurrence of a number, [0-9], followed by a character that is not a number: [^0-9]. In other words, it returns the index of the last number.

Thus, this:

SELECT LEFT(@str, PATINDEX('%[0-9][^0-9]%', @str )) AS Number,
       LTRIM(RIGHT(@str, LEN(@str) - PATINDEX('%[0-9][^0-9]%', @str ))) As Unit

returns the following:

Number	Unit
4000	ug/ML

Click here for a live demo.

If numeric data include double values as well, like:

DECLARE @str VARCHAR(MAX) = '4000.35 ug/ML'

In this case we can use:

SELECT LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))

Using PATINDEX on the REVERSE order of the string value gets us the index of the first digit from the end of the string that follows a character that is not a number. In our example the SELECT returns 7 indicating the index of number 5 from the end of the string.

Thus, this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str)))

gives us the numeric part, and this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))) AS Numeric,
       CASE 
          WHEN CHARINDEX ('%', @str) <> 0 THEN LTRIM(RIGHT(@str, LEN(@str) - CHARINDEX ('%', @str)))
          ELSE LTRIM(RIGHT(@str, PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))))
       END AS Unit

gives us both numeric and unit part.

Click here for a live demo.