Can this nested case when select be simplified?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can this nested case when select be simplified??

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2014-11-06 : 18:59:57

Is there a way to simplify the query below, I feel it is too convoluted for what it's trying to do; all the nested stuff. Or is it?

declare @urls table(uri varchar(250))
insert into @urls values
('http://test.adminX.com:1234/?abc=123/cde/fghi'),
('http://test.adminY.com/?abc=123/cde/fghi'),
('http://google.com:1234/?abc=123/cde/fghi'),
('http://ww2.google.com:1234/?abc=123/cde/fghi'),
('http://test.adminA.com:123'),
('http://test.adminB.com.xx:123'),
('http://test.adminB.co.uk:123'),
('http://123.456.789.251:123'),
('http://123.456.789.252'),
('http://123.456.789.253:1234/?abc=123/cde/fghi'),
('http://123.456.789.254'),
('http://123.456.789.255:345/'),
('https://test.admin1.com:1234/?abc=123/cde/fghi'),
('https://test.admin2.com/?abc=123/cde/fghi'),
('https://google.com:1234/?abc=123/cde/fghi'),
('https://ww2.google.com:1234/?abc=123/cde/fghi'),
('https://test.admin3.com:123'),
('https://123.456.789.251:123'),
('https://123.456.789.252'),
('https://123.456.789.253:1234/?abc=123/cde/fghi'),
('https://123.456.789.254'),
('https://123.456.789.255/'),
('123.456.789.255/'), --from here down leave as is but include them in result too
('dont.select.com:1234/?abc=123/cde/fghi'),
('test.admin.com/?abc=123/cde/fghi'),
('google.com:1234/?abc=123/cde/fghi'),
('http.google.com:1234/?abc=123/cde/fghi'),
('file://google.com:1234/?abc=123/cde/fghi')

Select case when CHARINDEX(':', url) > 0 then LEFT( url, CHARINDEX(':', url) - 1) else isnull(url, uri) end url -- strip port
from
(
select uri, case when CHARINDEX('/', url) > 0 then LEFT( url, CHARINDEX('/', url) - 1) else url end url --get host
from (
select uri, case when ( uri like 'http%' AND CHARINDEX('://', uri)>0) --clean only starting with http
then (SUBSTRING( uri, CHARINDEX('://', uri) + 3, 1000)) end url --strip protocol
from @urls
) x
)y

Thanks!

--PhB

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-07 : 09:38:08

Here's a different approach. Note the the execution plan is really simple, since SQL converts all the cross applys into scalar computations calls:


SELECT CASE 
          WHEN ishttp.yes <> 1
               THEN afterprotocol.uri
          WHEN colon.ix > 0
               THEN substring(afterprotocol.uri, 1, colon.ix - 1)
          WHEN slash.ix > 0
               THEN substring(afterprotocol.uri, 1, slash.ix - 1)
          ELSE afterprotocol.uri
          END AS host
FROM @urls url
CROSS APPLY (
     SELECT CASE 
               WHEN uri LIKE 'http%://%'
                 OR uri LIKE 'http://%'
                    THEN 1
               ELSE 0
               END
     ) ishttp(yes)
CROSS APPLY (
     SELECT CASE ishttp.yes
               WHEN 1
                    THEN SUBSTRING(uri, CHARINDEX('://', uri) + 3, len(url.uri))
               ELSE uri
               END
     ) afterprotocol(uri)
CROSS APPLY (
     SELECT charindex(':', afterprotocol.uri)
     ) colon(ix)
CROSS APPLY (
     SELECT charindex('/', afterprotocol.uri)
     ) slash(ix)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources