Reducing redundancy/duplication in SQL Code

Question

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?

Answer 1

;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)

Answer 2

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
)