Some Usefull Queries - Oracle

Use of Oracle Functions:-
SELECT ID, CUSTOMER_NAME, REPLACE(REPLACE(CUSTOMER_NAME,' ',''),'.') AS CUSTOMER_NAME_C,
ROW_NUMBER() OVER(ORDER BY REPLACE(REPLACE(CUSTOMER_NAME,' ',''),'.') as ROWNUMBER FROM CLOSE_MATCH
above function is being used for replacing all the spaces and Dots from the Sting of words.

SELECT SUBSTR(T.STRING, INSTR(T.STRING,' ',-1) +1 LAST_WORD
FROM (SELECT 'SEARCH THE LAS WORD' STRING FROM DUAL) T;
Above query will give the last word from the string of words [WORD]

SELECT SUBSTR(T.STRING, INSTR(T.STRING,' ',1) +1 LAST_WORD
FROM (SELECT 'SEARCH THE LAS WORD' STRING FROM DUAL) T;
Above query will give the first word from the string of words [SEARCH]

SELECT
IDNUMBER,
ADDRESS,
STRING1,
COUNT(STRING1) OVER (PARTITION BBY STRING1 ORDER BY STRING1) AS COUNT_STRING
FROM
(
SELECT IDNUMBER,
ADDRESS,
SUBSTR(TRIM(REPLACE(REPLACE(REPLACE(ADDRESS1,',',''),'.',''),'"','')),1,
INSTR(TRIM(REPLACE(REPLACE(REPLACE(ADDRESS1,',',''),'.',''),'"','')),' ',1)) AS
STRING1
FROM
ADDRESS_TABLE
)DT

Above query is to remove all the unwanted chars such as dot, dash, double quotes and make a group of records by thier first word similarity.


Validation for E-mail ID:-
select EMAIL, valid from (
select distinct EMAIL,
case
  when REGEXP_LIKE(EMAIL,'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') then 'yes'
   else 'no'
  end as Valid
from Table) where Valid='no'

Validation for Phone Number :- You want to determine whether a user entered a North American phone number in a common format, including the local area code. These formats include 1234567890, 123-456-7890, 123.456.7890, 123 456 7890, (123) 456 7890, and all related combinations. If the phone number is valid, you want to convert it to your standard format, (123) 456-7890, so that your phone number records are consistent.

select phone, valid from (
select distinct phone,
case
  when REGEXP_LIKE(phone,'^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$') then 'yes'
   else 'no'
  end as Valid
from Table) where  phone like '%)'

For finding multiple values associated with same key in one table, to count thier total occurence (Multiple Sites with Same Address )

select count(*), sum(noofsites) from (
select a.*, (LENGTH(a.sites) - LENGTH(REPLACE(a.sites, ',', ''))+1) NoofSites 
from (
 select number, name, address1, city, country, listagg(code, ',') within group (order by code ) Sites
 from (select asu.number, al.code , asu.name, al.address1, al.city, al.state, al.country
 from table1 asu, table2 al
 where asu.number = al.number
     )  group by number, name, address1, city, country ) a
where instr(sites,',')>0
order by number
)