regular expressions example in postgresql And FUZZYSTRMATCH

SELECT 
	summons_number, 
    CASE WHEN 
    	summons_number IN (
          SELECT 
  			summons_number 
  		  FROM 
  			parking_violation 
  		  WHERE 
            -- Match violation_time for morning values
  			violation_time SIMILAR TO '\d\d\d\dA' --4numberdigit-then start with A uppercase
    	)
        -- Value when pattern matched
        THEN 1 
        -- Value when pattern not matched
        ELSE 0 
    END AS morning 
FROM 
	parking_violation;

In this exercise, you will populate the morning column by matching patterns for violation_times occurring in the morning.
------------------------------------
pattern is ‘[^\w]+’, which means replacing everything that is not number, digit, underline with the nothing.

In the above code, the source is ‘some12-34@#$’, and the pattern is ‘\W+’ that represents all the characters except alphabets and numbers, the replacement string is a double single quote that represents nothing.

The ‘g’ is used to tell that apply this operation to all non-alphanumeric.
------------------------------------------------


SELECT 
	summons_number,
	-- Replace uppercase letters in plate_id with dash
	REGEXP_REPLACE(plate_id, '[A-Z]', '-', 'g') 
FROM 
	parking_violation;

----------FUZZYSTR MATCH--------
create extension fuzzystrmatch;
SELECT name FROM pg_available_extensions; ---display extension
select soundex('test'), soundex('tost'),difference('test','tost');
select soundex('indonesia'), soundex('indoneyssa'); --- returns the total similiarty between two string
difference('test','tost'); -- retrun the similiarity between two string from 0 - 4

---updating the table
update negara_tabel
set nama_negara = 'Indonesia'
where difference(nama_negara, 'Indonesia') = 4
------------------------------------------------------------------
EXAMPLE
--------------------------------------------------
SELECT
  summons_number,
  vehicle_color
FROM
  parking_violation
WHERE
  -- Match SOUNDEX codes of vehicle_color and 'GRAY'
  DIFFERENCE(vehicle_color, 'GRAY') = 4;

---------
UPDATE 
	parking_violation
SET 
	-- Update vehicle_color to `GRAY`
	vehicle_color = 'GRAY'
WHERE 
	summons_number IN (
      SELECT
        summons_number
      FROM
        parking_violation
      WHERE
        DIFFERENCE(vehicle_color, 'GRAY') = 4 AND
        -- Filter out records that have GR as vehicle_color
        vehicle_color != 'GR'
);
-------------------- EXAMPLE NO 2
SELECT 
	summons_number,
	vehicle_color,
    -- Include the DIFFERENCE() value for each color
	DIFFERENCE(vehicle_color, 'RED') AS "red",
	DIFFERENCE(vehicle_color, 'BLUE') AS "blue",
	DIFFERENCE(vehicle_color, 'YELOW') AS "yellow"
FROM
	parking_violation
WHERE 
	(
      	-- Condition records on DIFFERENCE() value of 4
		DIFFERENCE(vehicle_color, 'RED') = 4 OR
		DIFFERENCE(vehicle_color, 'BLUE') = 4 OR
		DIFFERENCE(vehicle_color, 'YELOW') = 4
	)
   	    -- Exclude records with 'BL' and 'BLA' vehicle colors
	) AND vehicle_color NOT SIMILAR TO 'BLA?'

------
UPDATE

UPDATE 
	parking_violation pv
SET 
	vehicle_color = CASE
      -- Complete conditions and results
      WHEN red = 4 THEN 'RED'
      WHEN blue = 4 then 'BLUE'
      WHEN yellow = 4 then 'YELLOW'
	END
FROM 
	red_blue_yellow rby
WHERE 
	rby.summons_number = pv.summons_number;

SELECT * FROM parking_violation LIMIT 1000;

---EXAMPLE 3
--
Complete the query using the regular expression pattern ' +' so that one or more consecutive space characters in the parking_held column are replaced with a single space (' ').
--
SELECT 
	LPAD(event_id, 10, '0') as event_id, 
    -- Replace consecutive spaces with a single space
    REGEXP_REPLACE(INITCAP(parking_held), ' +', ' ', 'g')  as parking_held
FROM 
    film_permit;

-----EXAMPLE 4
Write a query returning records with a registration_state that does not match two consecutive uppercase letters.
----------------------------------
SELECT
  summons_number,
  plate_id,
  registration_state
FROM
  parking_violation
WHERE
  -- Define the pattern to use for matching
  registration_state NOT SIMILAR TO '[A-Z]{2}';

Regular expressions must be enclosed in quotation marks ('').
The pattern is a regular expression, therefore, the SIMILAR TO operator is required in the WHERE clause.
As an example, to match three consecutive digits between 1 and 5, the pattern [1-5]{3} can be used.

--------------EXAMPLE 5
Write a query returning records with a vehicle_make not including an uppercase letter, a forward slash (/), or a space (\s).

SELECT
  summons_number,
  plate_id,
  vehicle_make
FROM
  parking_violation
WHERE
  -- Define the pattern to use for matching
  vehicle_make NOT SIMILAR TO '[A-Z/\s]+';