Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert CamelCase to snake_case

Tags:

sql

postgresql

need result of following query

select regexp_replace('StackOverflow', 'something', 'something')

as

stack_overflow
like image 492
Yusuf Avatar asked Dec 11 '22 14:12

Yusuf


1 Answers

The following regex adds an underscore in front of every uppercase letter:

regexp_replace(name, '([A-Z])','_\1', 'g'))

As that results in on underscore at the beginning, this needs to be removed using trim()

trim(both '_' from lower(regexp_replace(name, '([A-Z])','_\1', 'g')))

The following query:

with names (name) as (
  values ('StackOverflow'), 
         ('Foo'), 
         ('FooBar'), 
         ('foobar'), 
         ('StackOverflowCom')
)
select name, trim(both '_' from lower(regexp_replace(name, '([A-Z])','_\1', 'g'))) as new_name
from names;

returns:

name             | new_name          
-----------------+-------------------
StackOverflow    | stack_overflow    
Foo              | foo               
FooBar           | foo_bar           
foobar           | foobar            
StackOverflowCom | stack_overflow_com
like image 142
a_horse_with_no_name Avatar answered Dec 26 '22 15:12

a_horse_with_no_name