CODEWITHSUNDEEP
sqlsql-servercoding-style
MatBailie
MatBailie

Reputation: 86706

Reducing redundancy/duplication in SQL Code

In this question on stackoverflow, the accepted answer involves repeating the same code snippets several times. In my experience, many people strive to reduce that by encapsulating the code snippets in various ways, for various reasons;
- Maintainability (fewer places to change)
- Readability (read the code once, then it's 'alias' each subsequent time)
- etc, etc

Using the code in the linked question, how would you set out to reduce the repetition of identical code snippets? Or would you just leave it as-is?

(Not using alternative logic, but sticking to the use of REPLACE, etc, just changing the presentaiton of the same logic.)

Select  Case
        When CharIndex('.', Replace(URL, 'www.','')) > 0 then
           Left(Replace(URL, 'www.',''), CharIndex('.',Replace(URL, 'www.',''))-1)
        Else
           Replace(URL, 'www.','')
        End as [CleanURL]
From dbo.YourTable

(The accepted answer changed, so I copied the code example to here.)

EDIT

Just to clarify, as I think I have caused confusion.

This is not:
- How do I encapsulate this entire piece of code for re-use?

But it is rather:
- How do I reduce rendundancy within this piece of code?

Upvotes: 9

Views: 4503

Answers (6)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

;with cte as
(
  select replace(URL, 'www.', '')+'.' as url
  from myTable
)
select
  left(url, charindex('.', url)-1)
from cte

Edit 1 You can use a nested select that does the repeated code. Not really any different than using a cte.

Select  Case
        When CharIndex('.', URL) > 0 then
           Left(URL, CharIndex('.',URL)-1)
        Else
           URL
        End as [CleanURL]
From 
  (select Replace(URL, 'www.','') as URL
   from myTable) as T

Edit2 Removed the duplicate charindex. Using cross apply

select
  case
    when c2.idx > 0 then
      left(c1.url, c2.idx)
    else
      c1.url
  end
from myTable as m
  cross apply (select replace(m.URL, 'www.', '')) as c1(url)
  cross apply (select charindex('.', c1.url)) as c2(idx)

Upvotes: 6

MatBailie
MatBailie

Reputation: 86706

Inspired by UW Concept, but using a table valued function and sub_query to avoid the performance penalties associated with the suggest scalar function and use of an internal variable.

SELECT
  myTable.*,
  cleanup.domain
FROM
  myTable
CROSS APPLY
  dbo.CleanupURL(myTable.myURL) as cleanup

Using the following function...

CREATE FUNCTION
  dbo.CleanupUrl(@urlstring nvarchar(200))
RETURNS TABLE
AS
RETURN
(
  SELECT
    CASE WHEN suffix_pos = 0 THEN
      myURL
    ELSE
      LEFT(myURL, suffix_pos - 1)
    END AS domain
  FROM
  (
    SELECT
      myURL,
      CharIndex('.', myURL) AS suffix_pos
    FROM
    (
      SELECT
        REPLACE(@urlstring, 'www.', '') as myURL
    )
    AS no_prefix
  )
  AS suffix_found
)

Upvotes: 1

Steven Ryssaert
Steven Ryssaert

Reputation: 1967

This is an example with a function

SELECT dbo.CleanupUrl(URL) AS CleanURL FROM yourTable;

The function that takes care of the formatting

CREATE FUNCTION dbo.CleanupUrl(@urlstring nvarchar(200))
RETURNS nvarchar(200)
AS
BEGIN
  DECLARE @SReturn nvarchar(200), @ReplacedURL nvarchar(200)
  SET @ReplacedURL = Replace(@urlstring, 'www.','')
  SELECT @SReturn = Case
        When CharIndex('.', @ReplacedURL) > 0 then
           Left(@ReplacedURL, CharIndex('.',@ReplacedURL)-1)
        Else
           @ReplacedURL
        End

RETURN @SReturn
END

You can then call this function from every query .

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

SELECT
  myTable.*,
  LEFT(noPrefix.myURL, domain_end.pos) AS domain
FROM
  myTable
CROSS APPLY
  (SELECT REPLACE(myTable.myURL, 'www.', '') AS myURL) AS [noPrefix]
CROSS APPLY
  (SELECT CHARINDEX('.', noPrefix.MyURL) AS pos) AS [suffix_start]
CROSS APPLY
  (SELECT CASE WHEN suffix_start.pos = 0 THEN LEN(noPrefix.myURL) ELSE suffix_start.pos - 1 END AS pos) AS [domain_end]

Upvotes: -1

StackExchange saddens dancek
StackExchange saddens dancek

Reputation: 3378

The WITH clause should be quite suitable for this:

WITH replaced_urls AS (
    SELECT Replace(URL, 'www.','') AS url,
           CharIndex('.', Replace(URL, 'www.','')) AS idx
    FROM dbo.YourTable
)
Select  Case
    When idx > 0 then
       Left(url, idx-1)
    Else
       url
    End as [CleanURL]
From replaced_urls

This creates a temporary table that exists for the duration of the query, containing the results that are used multiple times. (I didn't test this, but should work.)

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

WITH
  prefix_removed
AS
(
  SELECT
    *,
    REPLACE(URL, 'www.','') AS myURL_NoPrefix
  FROM
    myTable
)
,
  suffix_start
AS
(
   SELECT
     *,
     CharIndex('.', myURL_NoPrefix) AS domain_end
   FROM
     prefix_removed
)
SELECT
  CASE WHEN
    doamin_end = 0
  THEN
    myURL_NoPrefix
  ELSE
    LEFT(myURL_NoPrefix, domain_end-1)
  END AS domain
FROM
  prefix_removed

Upvotes: -1

Related Questions