CODEWITHSUNDEEP
sqlif-statementswitch-statementcase
user5832647
user5832647

Reputation:

SQL Case Statement - how to do it

I have the below statement which works nicely in Tableau.

However, I'd like to turn this into SQL and save all the results into a temporary column name.

Does anyone know how I might do this?

Essentially, the below removes the .com/.net etc... from the domain name.

I then have another script that removes the subdomain (everything from the first . to the left of the resulting value).

If anyone can help me with these, that would be incredible as I'm not sure how to do this in SQL

IF CONTAINS([domain], ".co.uk") then LEFT([domain],LEN([domain])-6)
elseif  CONTAINS([domain], ".com") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".net") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".org") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".biz") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".edu") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".ac") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".gov") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".biz") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".co") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".ca") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".io") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".in") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".it") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".uk") then LEFT([domain],LEN([domain])- 3)
elseif  CONTAINS([domain], ".ru") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".ie") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".tv") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".info") then LEFT([domain],LEN([domain])-5)
elseif  CONTAINS([domain], ".fr") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".es") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".pl") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".is") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".hu") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".xxx") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".nl") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".ro") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".xyz") then LEFT([domain],LEN([domain])-4)
elseif  CONTAINS([domain], ".no") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".eu") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".me") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".cz") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".fi") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".nl") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".al") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".am") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".af") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".st") then LEFT([domain],LEN([domain])-3)
elseif  CONTAINS([domain], ".cn") then LEFT([domain],LEN([domain])-3)
else [domain]
end

Upvotes: 0

Views: 93

Answers (5)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use Regular expression as below

select case when v like '%.[a-z][a-z][a-z]' then left(v, len(v)-4) 
            when v like '%.[a-z][a-z].[a-z][a-z]' then left(v, len(v)-6) 
            when v like '%.[a-z][a-z]' then left(v, len(v)-3)  end from #yourTable

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82000

If SQL Server, perhaps something like this

Declare @YourTable table (domain varchar(100))
Insert Into @YourTable values
('somedomain.org'),
('somedomain.net'),
('anotherdomain.az'),
('nodomain'),
('somedomain.com?Param=1')

Select A.*
      ,NoDomain = left(domain,len(domain)-charindex('.',reverse(domain)))
 From  @YourTable A

Returns

domain                  NoDomain
somedomain.org          somedomain
somedomain.net          somedomain
anotherdomain.az        anotherdomain
nodomain                nodomain
somedomain.com?Param=1  somedomain

Upvotes: 2

etsa
etsa

Reputation: 5060

I suggest a different approach: Create a table with a list of the suffix you want to purge) and make a general substition. I did it in MSSQL (but you can adapt for other DB with small changes).

Anyway (in your solution too) be sure that the characters your are going to purge cannot be in the middle of the text (eg. http://test.com.blabla.us)

dot_nation is the table with contains all the suffix to purge. It is just an example and I think you have to work on it.

CREATE TABLE dot_nation
(
SUFFIX VARCHAR(10) NOT NULL
);
ALTER TABLE dot_nation ADD CONSTRAINT dot_nation_PK PRIMARY KEY (SUFFIX);

INSERT INTO dot_nation VALUES('.co.uk');
INSERT INTO dot_nation VALUES('.com');
INSERT INTO dot_nation VALUES('.org');
INSERT INTO dot_nation VALUES('.fr');

CREATE TABLE demo (ID INT, SITE_ADDRESS VARCHAR(500));
INSERT INTO demo VALUES (1, 'http:/xxxxx.com');
INSERT INTO demo VALUES (2, 'http:/xxxxx.fr');
INSERT INTO demo VALUES (3, 'http:/org.en');
INSERT INTO demo VALUES (4, 'http:/yyyyyy.co.uk');

SELECT demo.*, LEFT(demo.SITE_ADDRESS, LEN(demo.SITE_ADDRESS)-LEN(suffix)) AS ADDRESS_CLEANED
FROM demo
LEFT JOIN dot_nation ON SITE_ADDRESS LIKE '%'+SUFFIX+'%'

Output:

    ID          SITE_ADDRESS         ADDRESS_CLEANED
----------- -------------------- --------------------
1           http:/xxxxx.com      http:/xxxxx
2           http:/xxxxx.fr       http:/xxxxx
3           http:/org.en         NULL
4           http:/yyyyyy.co.uk   http:/yyyyyy

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133390

if You could use CASE WHEN this way the ... mean that you must addapr the code properly

select   CASE domain
            WHEN  domain like "%.co.uk" THEN LEFT(domain,LENGTH(domain)-6)
            WHEN  domain LIKE "%. com"  THEN LEFT(domain,LENGTH(domain)-4)
            WHEN  domain LIKE "%. net"  THEN LEFT(domain,LENGTH(domain)-4)
            .....
            WHEN domain LIKE "%.co" THEN LEFT(domain,LENGTH(domain)-3)
            WHEN domain LIKE "%.ca" THEN LEFT(domain,LENGTH(domain)-3)
            .... 
          END 
FROM MY_TABLE

Upvotes: 0

Jeremy Real
Jeremy Real

Reputation: 766

It's quite similar to what you have now, but use like instead of contains:

IF domain like '%.co.uk' THEN left(domain,len(domain)-6)
IF domain like '%.com' THEN left(domain,len(domain)-4)
...
ELSE domain END AS stripped_domain

Upvotes: 0

Related Questions