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]+';
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter