Help with string formatting in SQL Server Query

Question

Hello,

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:

• 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!

Answer 1

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

Answer 2

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)