CODEWITHSUNDEEP
sqlsql-serverdatabase
Tommen
Tommen

Reputation: 123

How can I change the results of a single column of a sql query, within the query itself?

I'm running the below SQL query:

SELECT
    share_name AS "Server", 
    Capacity, Available,
    ((available * 1.00) / capacity) * 100 as "% Free"
FROM 
    storage_info
WHERE 
    share_name LIKE '%192.168.1.1%'
    OR share_name LIKE '%192.168.1.2%'
    OR share_name LIKE '%192.168.1.3%'
    OR share_name LIKE '%192.168.1.4%';

Which returns the results of the 'share_name' column as IP addresses, as they are stored in the table.

Is it possible to change what the output of the IP addresses will be, within the query - in the same way that 'AS' changes the name of the column?

Upvotes: 1

Views: 108

Answers (4)

Paul Lucaciu
Paul Lucaciu

Reputation: 134

You can do it as follows:

SELECT
    CASE 
        WHEN 
            share_name = '192.168.1.1' 
        THEN 
            'Server 1' 
        ELSE 
            share_name 
    END AS "Server", 
    Capacity, Available,
    ((available * 1.00) / capacity) * 100 as "% Free"
FROM 
    storage_info
WHERE 
    share_name LIKE '%192.168.1.1%'
    OR share_name LIKE '%192.168.1.2%'
    OR share_name LIKE '%192.168.1.3%'
    OR share_name LIKE '%192.168.1.4%';

Upvotes: 2

Linger
Linger

Reputation: 15058

SELECT CASE WHEN share_name LIKE '%192.168.1.1%' THEN 'SERVER 1'
            WHEN share_name LIKE '%192.168.1.2%' THEN 'SERVER 2'
            WHEN share_name LIKE '%192.168.1.3%' THEN 'SERVER 3'
            WHEN share_name LIKE '%192.168.1.4%' THEN 'SERVER 4'
            ELSE share_name END AS "Server",
       Capacity, Available, ((available * 1.00) / capacity) * 100 as "% Free"
FROM storage_info
WHERE share_name LIKE '%192.168.1.1%'
   OR share_name LIKE '%192.168.1.2%'
   OR share_name LIKE '%192.168.1.3%'
   OR share_name LIKE '%192.168.1.4%';

Upvotes: 1

Michael McGriff
Michael McGriff

Reputation: 803

You can use this to get the desired output. Just add a case for each IP you want to output differently.

SELECT
    CASE WHEN share_name LIKE '%192.168.1.1%' THEN 'Server 1' END AS "Server", 
    Capacity, Available,
    ((available * 1.00) / capacity) * 100 as "% Free"
FROM 
    storage_info
WHERE 
    share_name LIKE '%192.168.1.1%'
    OR share_name LIKE '%192.168.1.2%'
    OR share_name LIKE '%192.168.1.3%'
    OR share_name LIKE '%192.168.1.4%';

You probably don't need the leading wildcard on your WHERE conditions which will speed this query greatly.

Upvotes: 1

mxix
mxix

Reputation: 3659

;WITH map([IP],[NAME]) AS (
    select
        [IP],[NAME]
    from (values
        ('192.168.1.1','Server1'),
        ('192.168.1.2','Server2')
)
SELECT
    ISNULL(map.name,share_name) AS "Server", 
    Capacity, Available,
    ((available * 1.00) / capacity) * 100 as "% Free"
FROM 
    storage_info
LEFT JOIN map on
    storage_info.share_name = map.IP
WHERE 
    share_name LIKE '%192.168.1.1%'
    OR share_name LIKE '%192.168.1.2%'
    OR share_name LIKE '%192.168.1.3%'
    OR share_name LIKE '%192.168.1.4%';

Upvotes: 1

Related Questions