CODEWITHSUNDEEP
sqlsql-serversyntaxcode-duplication
user5441558
user5441558

Reputation:

Remove code-duplication in SQL query

Is there a way to remove the multiple repetitions of: LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1) in the following query?

SELECT 
    ISNULL(LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1), 'Total'), 
    Count([Id]) 
FROM [dbo].[Ids]
GROUP BY ROLLUP(LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1))
ORDER BY 
    GROUPING(LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1)), 
    COUNT([Id]) DESC,
    LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1)

Even though I have a specific exemple I'm hoping to find a generic solution to this problem.

Upvotes: 1

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method uses subqueries. Another CTEs. I like a third way, lateral joins using apply:

SELECT COALESCE(v.x, 'Total'), 
       Count(*) 
FROM [dbo].[Ids] i OUTER APPLY
     (VALUES (LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1) )
     ) v(x)
GROUP BY ROLLUP(v.x)
ORDER BY GROUPING(v.x), 2 DESC

Upvotes: 3

JeffUK
JeffUK

Reputation: 4241

If you do this across multiple queries you could create a user defined scalar function 

CREATE FUNCTION Patterniser(@ID varchar(100))  
RETURNS varchar(100)  
AS  
BEGIN  
   DECLARE @Patterned varchar(100)  
        @Patterned= LEFT(@ID, PATINDEX('%[0-9]%', @ID) - 1)
    RETURN @Patterned
END

Then call dbo.Patterniser([Id]) wherever you wish to use it

Upvotes: 2

Related Questions