Change some fields in a query-CodePudding

I'm using the following query to get a lis of server:

select 
                                DISTINCT upper("Local_Hostname_U")

                            from 
                                "Services_Inventory_610"
                            where 
                                CASE
                                   --WHEN upper("Local_Hostname_U")='EAP-WEBSERVICES.WEB.INPS' THEN upper("Local_Hostname_U")='WEBINPS59'
                                   WHEN "TMZDIFF"=-3600 THEN  ("Interval_Begin_Time">=1230110230000000 and "Interval_Begin_Time"<1230111230000000)
                                   WHEN "TMZDIFF"=-7200 THEN  ("Interval_Begin_Time">=12301100000000 and "Interval_Begin_Time"<1230110230000000)
                                END
                                order by upper("Local_Hostname_U")

I get the following list:

CUSTMON03.SERVIZI.INPS
EAP-WEBSERVICES.WEB.INPS
JAVAZAPPWS-P01
JAVAZAPPWS-P02
JAVAZAPPWS-P03
JAVAZAPPWS-P04
JAVAZAPPWS-P05
JAVAZAPPWS-P06
JAVAZAPPWS-P07
JAVAZAPPWS-P08
LHEWBSWAS01
LHEWBSWAS02
LHEWBSWAS03
LHEWBSWAS04
LHEWBSWAS05
LHEWBSWAS06
LHEWBSWAS07
LHEWBSWAS08
LHEWBSWAS09
LHEWBSWAS10
LHEWBSWAS11
LHEWBSWAS12
WEBINPS120.SERVIZI.INPS
WEBINPS52
WEBINPS53
WEBINPS54
WEBINPS55
WEBINPS56.SERVIZI.INPS
WEBINPS58
WEBINPS62.SERVIZI.INPS

Is there a way to substitute EAP-WEBSERVICES.WEB.INPS with WEBINPS59 and to delete the string ".SERVIZI.INPS" from the list where it is in the same query?

CodePudding user response：

try with split_part if you use postgreSQL:

      select DISTINCT upper(case when "Local_Hostname_U" LIKE '%-%' then split_part(split_part("Local_Hostname_U",'-',2),'.',1) 
  else split_part("Local_Hostname_U",'.',1) end)
                                    from 
                                        "Services_Inventory_610"
                                    where 
                                        CASE
                                           --WHEN upper("Local_Hostname_U")='EAP-WEBSERVICES.WEB.INPS' THEN upper("Local_Hostname_U")='WEBINPS59'
                                           WHEN "TMZDIFF"=-3600 THEN  ("Interval_Begin_Time">=1230110230000000 and "Interval_Begin_Time"<1230111230000000)
                                           WHEN "TMZDIFF"=-7200 THEN  ("Interval_Begin_Time">=12301100000000 and "Interval_Begin_Time"<1230110230000000)
                                        END
                                        order by upper("Local_Hostname_U")

This split_part fuction show only what is before .

CodePudding user response：

select split_part(name,'.',1)  from hostname ;

CodePudding user response：

Try this:

   select  DISTINCT REPLACE(REPLACE(upper("Local_Hostname_U"), 'EAP-WEBSERVICES.WEB.INPS', 'WEBINPS59 '), '.SERVIZI.INPS', '')
    from "Services_Inventory_610"
    where 
        CASE
        --WHEN upper("Local_Hostname_U")='EAP-WEBSERVICES.WEB.INPS' THEN upper("Local_Hostname_U")='WEBINPS59'
            WHEN "TMZDIFF"=-3600 THEN  ("Interval_Begin_Time">=1230110230000000 and "Interval_Begin_Time"<1230111230000000)
            WHEN "TMZDIFF"=-7200 THEN  ("Interval_Begin_Time">=12301100000000 and "Interval_Begin_Time"<1230110230000000)
        END
    order by upper("Local_Hostname_U")