Extracting a string using SQL PATINDEX, substring of varying sizes

Question

I'm trying to extract ###x###, ###x##, and sometimes #x#. Sometimes there may be a space between the numbers and the x. Essentially, I may run into strings like

• 720x60
• 720x600
• 720 x 60
• 720_x_60
• 1x1

I use PATINDEX() to find the first occurrence of the pattern '%[0-9]%x%[0-9]%'. So far so good. Then I use PATINDEX() to find the first occurence of a non-digit string after that. This is where I have trouble. I get results as in the screenshot. Code is also below.

SELECT *
    ,CASE WHEN StartInt > 0
        THEN SUBSTRING(Placement, StartInt, SizeLength) ELSE NULL END AS PlacementSize
FROM
(SELECT Placement
    --find the first occurrence of #*x*#
    ,PATINDEX('%[0-9]%x%[0-9]%',Placement) AS StartInt

    --find the first non-digit after that
    ,PATINDEX(
        '%[^0-9]%'
        ,RIGHT(
            Placement + '_' --this underscore adds at least one non-digit to find
            ,LEN(Placement)
                -
            PATINDEX('%[0-9]%x%[0-9]%',Placement) - 5
            )
        ) + 6 AS SizeLength
FROM [Staging].[Client].[A01_FY14_Reporting_staging]
WHERE [Date] > '2014-07-01') AS a

Results:

Answer 1

If you're dealing with a pair of numeric values, but are also dealing with dirty data, and lack the power of Regex, here's what you can do in TSQL.

Essentially, it looks like you're wanting to break the string in half at 'x', then whittle down the outputs until you have numeric only values. Using a set of derived tables, this becomes relatively easy (and not as hard to read)

declare @placements table (Placement varchar(10))
insert into @placements values 
('720x60'),
('720x600'),
('720 x 60'),
('720_x_60'),
('1x1')

SELECT LEFT(LeftOfX,PATINDEX('%[^0-9]%',LeftOfX) - 1) + 'x' + RIGHT(RightOfX, LEN(RightOfX) - PATINDEX('%[0-9]%', RightOfX) + 1)
FROM (
    SELECT RIGHT(LeftOfX, LEN(LeftOfX) - PATINDEX('%[0-9]%', LeftOfX) + 1) AS LeftOfX, LEFT(RightOfX, LEN(RightOfX) - PATINDEX('%[0-9]%', REVERSE(RightOfX)) + 1) AS RightOfX
    FROM (
        SELECT LEFT(p.Placement,x) AS LeftOfX, RIGHT(p.Placement,LEN(p.Placement) - x + 1) AS RightOfX
        FROM (
            SELECT
                  p.Placement
                , CHARINDEX('x',p.Placement) AS x
            FROM @placements p
            ) p
        ) p
    ) p

Here's the SQLFiddle example.

First, select your placement, the location of your 'x' in Placement, and other columns you want from the table. Pass the other columns up through the derived tables.

Next, Split the string into Left and Right.

Process left and right in two more queries, the first to take the right of results starting at the numeric portion, then the left of the results ending at the non-numeric portion.

EDIT: Fixed the outputs, both numbers now selected.