Removing parts of a server name using SQL

Question

I have a table that has a bunch of different servers most of them are listed as servername.ad.edu. I want to remove everything after the first dot. so it just shows as servername in my table. The code I was provided for the population of the table was not written by me but I did add the LEFT statement to see if I could remove it during population. Here is my code...

DELETE FROM clean_tanium_server;
MERGE clean_tanium_server AS Target
USING tanium_server AS Source
ON Source.computer_id = Target.computer_id AND Source.[ci_installed_application name] = Target.application_name
WHEN NOT MATCHED BY Target THEN
    INSERT (computer_id, computer_name, operating_system, application_name, application_normalized_name, chassis_type, cpu_core, cpu_processor, ip_address)
    VALUES (Source.computer_id, Source.computer_name, Source.operating_system, Source.[ci_installed_application name], Source.[ci_installed_application normalized_name], Source.chassis_type, Source.cpu_core, Source.cpu_processor, Source.ip_address)
WHEN MATCHED THEN UPDATE SET
    *Target.computer_name = LEFT(Source.computer_name, CHARINDEX('.', Source.computer_name) - 1),*
    Target.operating_system = Source.operating_system,
    Target.application_normalized_name = Source.[ci_installed_application normalized_name]
WHEN NOT MATCHED BY Source THEN
    DELETE;

So I can't figure out why when I populate the tables they aren't omitting the .ad.edu portion. Any help is greatly appreciated. Thank you.

How it looks currently:

computer_name
servername1.ad.edu
servername2.ad.edu
servername3.us.edu

How I want it to look:

computer_name
servername1
servername2
servername3

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Jan 20 '23 at 21:22
These are great questions and I will go back and accept the answers on my previous ones. But as far as this question, I am not too keen on SQL and just had this thrown at me. So I am trying to muddle through as much as possible — giff1, Jan 20 '23 at 21:23
@DaleK I updated my example but not all will be .ad.edu, some will be .us.edu amongst other things — giff1, Jan 20 '23 at 21:27

score 1 · Accepted Answer · answered Jan 20 '23 at 21:29

1

You're only applying the LEFT changes when entering the WHEN MATCHED THEN UPDATE SET block, but as pointed out in the comments, you're emptying the table first, so you'll never match and will only execute the WHEN NOT MATCHED BY Target THEN block which doesn't have your LEFT modifications

answered Jan 20 '23 at 21:29

Lee

149
1
8

I figured it out because of you. Thank you so much I really appreciate it and I will take some SQL online classes so when they ask me to do more things in SQL I will be ready. Thanks again and thank you to everyone here. – giff1 Jan 20 '23 at 21:46

score 1 · Answer 2 · answered Jan 20 '23 at 21:42

Here is a summary of the other answers and comments.

You don't need a MERGE statement because you delete all the rows from the table first - so you just need an INSERT
I would recommend using TRUNCATE rather than DELETE for deleting all rows - unless you have a specific need for it. Pros and Cons of Truncate over Delete
With your INSERT statement use the code that Aaron Bertrand provided to strip the end off your computer name.

TRUNCATE TABLE clean_tanium_server;

INSERT INTO clean_tanium_server (
    computer_id
    , computer_name
    , operating_system
    , application_name
    , application_normalized_name
    , chassis_type
    , cpu_core, cpu_processor
    , ip_address)
SELECT
    computer_id
    , LEFT(computer_name, CHARINDEX('.', computer_name + '.') - 1)
    , operating_system
    , [ci_installed_application name]
    , [ci_installed_application normalized_name]
    , chassis_type
    , cpu_core
    , cpu_processor
    , ip_address
FROM tanium_server;

Removing parts of a server name using SQL

2 Answers2