Geek Answers Handbook

Reducing Redundancy / Duplication in SQL Code

In this stackoverflow question, the accepted answer involves repeating the same code fragments several times. In my experience, many people tend to reduce this by encapsulating code fragments in different ways, for various reasons,
- Maintaining health (fewer places to change)
- Readability (read the code once, then it is "alias" every subsequent time)
- etc, etc

Using the code in a related question, how would you decide to reduce the repetition of identical code fragments? Or do you just leave it as it is?

(Do not use alternative logic, but stick to using REPLACE, etc., just by 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

( , .)

, , .

:
- ?

:
- ?

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

1. , . 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 charindex. 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)
+4

UW, sub_query, , .

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

...

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
)
+1
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
0

WITH :

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

, , , . ( , .)

0

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

,

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

.

0
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]
0

More articles:


All Articles