Problem with removing part of string while using join

Question

I'm trying to create a resources report using a select query against mutliple tables.
The report is fine except that i wish to remove part of a string on some values when presented.
Some of our resources names has the domain name included in the name like "myresource1.my.domain.com"
In the result of the query i don't want the domain part of name to be shown.

The table "Resources" has got the column RESOURCENAME which has the string for the resources names.

Example on how the Resources table looks like but it got many more columns than this.

RESOURCEID | RESOURCENAME
18456 | computer1.my.domain.com
26425 | computer2.local
36415 | tablet1 (without domain name but with text inside theese parentheses)
49400 | smartphone1 (IMEI code)
23544 | computer3.my.domain.com

What i got so far in the query is this but im missing the part where i select the Resources.RESOURCENAME but stripped from the domain part in the string.

SELECT
res.RESOURCEID 'Resource ID',
--Here i would like to get res.RESOURCENAME but if the string ends with .my.domain.com or .local i want that domain part of the string removed.
FROM Resources res
--left join 'Here i have a couple of left join that works as expected'
--left join 'Here i have a couple of left join that works as expected'

Anyone who can help me here?

Regards Larsa

Answer 1

Hi @Larsa
Please check this：

SELECT res.RESOURCEID,CASE WHEN PATINDEX('%[.(]%',RESOURCENAME)>1   
                           THEN LEFT(RESOURCENAME,PATINDEX('%[.(]%',RESOURCENAME)-1)   
                ELSE RESOURCENAME END AS RESOURCENAME  
FROM Resources res  
 --left join 'Here i have a couple of left join that works as expected'  
 --left join 'Here i have a couple of left join that works as expected'  

For more details about PATINDEX function , please refer to this document: PATINDEX (Transact-SQL)

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks alot for both suggestions.
I ended up using @LiHong-MSFT solution which what exactly what i was looking for :)

Answer 3

Try

DECLARE @res TABLE (ResourceId INT, ResourceName VARCHAR(4000))

INSERT INTO @res
SELECT *
FROM (VALUES
(18456,'computer1.my.domain.com'),
(26425,'computer2.local'),
(36415,'tablet1 (without domain name but with text inside theese parentheses)'),
(49400,'smartphone1 (IMEI code)'),
(23544,'computer3.my.domain.com')
) a(RESOURCEID,RESOURCENAME)

SELECT 
    ResourceId,
    CASE 
        WHEN CHARINDEX('.',ResourceName)>1 THEN LEFT(ResourceName,CHARINDEX('.',ResourceName)-1) 
        WHEN CHARINDEX(' ',ResourceName)>1 THEN LEFT(ResourceName,CHARINDEX(' ',ResourceName)-1) 
        ELSE ResourceName END as ResourceName
FROM @res