Search code examples
sqlsql-serverdelimitercharindex

Finding Substring With Delimiter SQL

Lets say I have a dimension, called "Custom1" that can be 47x29x10 , 01x8x300...etc.

How can I split on the "x" delimiter regardless of the length between the delimiters?

I was successfully able to capture the first and last numbers, but the middle number is giving me trouble.

Here is the code I have already

SELECT

TRIM(SUBSTRING(Custom1, 1, CHARINDEX('x',Custom1)-1)) as Length,

SUBSTRING(Custom1, CHARINDEX('x', Custom1) +1, LEN(Custom1) - CHARINDEX('x', Custom1)) as Widths,

REVERSE(SUBSTRING(REVERSE(Custom1),0,CHARINDEX('x',REVERSE(Custom1)))) as Height

FROM Table
Solution 
  • SELECT
    PARSENAME(REPLACE(Custom1, 'x', '.'), 3) as Length,
    PARSENAME(REPLACE(Custom1, 'x', '.'), 2) as Width,
    PARSENAME(REPLACE(Custom1, 'x', '.'), 1) as Height
FROM YourTable;