Help with string formatting in SQL Server Query

I have the following SQL query:

SELECT DISTINCT ProductNumber, PageNumber FROM table

I am trying to modify the query so that PageNumber will be formatted. You see, PageNumber is in any of the following formats, where 'x' is a digit:

• xxx, xxx
• xxx
• xxx-xxx
• xx, xxx-xxx
• xx-xx, xxx
• xx-xx, xxx-xxx

I want to format PageNumber so that it is only in the format: xxx. To do so, I have parse out the following bolded numbers from the above formats:

• 开发者_StackOverflow中文版xxx, xxx
• xxx
• xxx-xxx
• xx, xxx-xxx
• xx-xx, xxx
• xx-xx, xxx-xxx

I want to do this all without writing any functions, but I don't know if that is possible. I am having trouble "detecting" all of the different formats, though:

Here is what I have so far:

SELECT  ProductNumber,
        CASE WHEN CHARINDEX(',', PageNumber) > 0
             THEN SUBSTRING(PageNumber, 0, CHARINDEX('-', PageNumber))
             WHEN CHARINDEX('-', PageNumber) > 0
             THEN SUBSTRING(PageNumber, 0, CHARINDEX('-', PageNumber))
             ELSE PageNumber
        END AS PageNumber
FROM    table
WHERE   PageNumber IS NOT NULL
    AND PageNumber <> '' 

Can anyone offer me some help? Thanks!

Use pattern matching rather than CHARINDEX

CASE also forces ordering of evaluation which helps here for the 3rd case which overlaps with the first 2 cases.

Not tested, something like

CASE
    WHEN PageNumber LIKE '[0-9][0-9][0-9]%' THEN LEFT(PageNumber, 3)
    WHEN PageNumber LIKE '[0-9][0-9]-[0-9][0-9], [0-9][0-9][0-9]') THEN RIGHT(PageNumber , 3)
    WHEN PageNumber LIKE '[0-9][0-9]%') THEN LEFT(PageNumber, 2)
END

try this:

DECLARE @YourTable table (ProductNumber int, PageNumber varchar(20))
INSERT @YourTable VALUES (1,'123, 456')
INSERT @YourTable VALUES (2,'123')
INSERT @YourTable VALUES (3,'123-456')
INSERT @YourTable VALUES (4,'12, 345-678')
INSERT @YourTable VALUES (5,'12-34, 567')
INSERT @YourTable VALUES (6,'12-34, 567-789')

;WITH AllNumbers AS   ---builds a Numbers table 1-100
(   SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<101
)
, RowChars AS --one row for each non-numeric single character value per @YourTable row
(   SELECT DISTINCT
        ProductNumber,Number, SUBSTRING(PageNumber,Number,1) AS CharacterOF
        FROM @YourTable
            INNER JOIN AllNumbers  ON 1=1
        WHERE SUBSTRING(PageNumber,Number,1) IS NOT NULL AND SUBSTRING(PageNumber,Number,1) NOT LIKE '[0-9]' AND SUBSTRING(PageNumber,Number,1)!=''
)
,FirstSplit AS --get first non-numeric single character value per @YourTable row
(   SELECT
        ProductNumber,MIN(Number) AS SplitOf
        FROM RowChars
        GROUP BY ProductNumber
)
SELECT
    t.ProductNumber, LEFT(t.PageNumber,COALESCE(s.SplitOf-1,LEN(t.PageNumber))) AS NewPage,t.PageNumber AS OldPage
    FROM @YourTable                 t
        LEFT OUTER JOIN FirstSplit  s ON t.ProductNumber=s.ProductNumber

OUTPUT:

ProductNumber NewPage              OldPage
------------- -------------------- --------------------
1             123                  123, 456
2             123                  123
3             123                  123-456
4             12                   12, 345-678
5             12                   12-34, 567
6             12                   12-34, 567-789

(6 row(s) affected)