I have two columns one have values in Camel case 'Costa Rica' which is correct but the another column is all capital 'COSTA RICA' , sometime its make problem for me while querying , Can I convert these Capital into camel case too as first column?
2 Answers
Use initcap() function to get this done.
- to convert all values of second column having all capital letters use below mentioned query:
update your_table set second_col=initcap(second_col); - if you just want to compare the columns then use like below:
where first_col=initcap(second_col) or
where upper(first_col)=upper(second_col) 
Comments
-- Rename tables DO $$ DECLARE table_record RECORD; BEGIN -- Loop through each table FOR table_record IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE') LOOP EXECUTE FORMAT('ALTER TABLE public.%I RENAME TO %I', table_record.table_name, lower(regexp_replace(table_record.table_name, '(.)([A-Z])', '\1_\2', 'g'))); END LOOP; END $$; -- Rename columns DO $$ DECLARE table_record RECORD; column_record RECORD; BEGIN -- Loop through each table FOR table_record IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE') LOOP -- Loop through each column in the table FOR column_record IN (SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = table_record.table_name) LOOP EXECUTE FORMAT('ALTER TABLE public.%I RENAME COLUMN %I TO %I', table_record.table_name, column_record.column_name, lower(regexp_replace(column_record.column_name, '(.)([A-Z])', '\1_\2', 'g'))); END LOOP; END LOOP; END $$; 